CODEWITHSUNDEEP
sqlfiltergroup-bypowerbimeasure
Rhea
Rhea

Reputation: 53

GROUP BY Power BI along with FILTER

I want to GROUP BY the "id" column and then get a COUNT for records that equal "yes". I want to write a MEASURE in Power BI.

This is how my data looks :-

enter image description here

I would want to convert it like :-

enter image description here

Then, I want to just COUNT the number of records that equal "yes", like :-

"yes" has 3 DISTINCT records. "no" has 4 DISTINCT records.

I do know that if I remove duplicates and do some things here and there, I can actually get a COUNT for this... But I need those records for other reasons.

Is there any way I can write a MEASURE to achieve this.

Would really appreciate some help :)

Upvotes: 0

Views: 4848

Answers (1)

Peter
Peter

Reputation: 12375

Try this:

Grouped Table = 
SUMMARIZE(
    YourTable,
    YourTable[id],
    "status", MAX(YourTable[status])
)

Yes = 
CALCULATE(
    COUNTROWS('Grouped Table'),
    'Grouped Table'[status] = "Yes"
)

No = 
CALCULATE(
    COUNTROWS('Grouped Table'),
    'Grouped Table'[status] = "No"
)

Which looks like

enter image description here

OR: Add another Count Table if you like

Count Table = 
SUMMARIZE(
    'Grouped Table',
    'Grouped Table'[status],
    "count", COUNT('Grouped Table'[status])
)

enter image description here

Upvotes: 0

Related Questions