Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, Still pretty new to DAX, but I think i just need a little help to take this one step further... I'm actually working in Power Pivot here though fyi... I've got a measure that evaluates to True or False for all the records in my table. What i'd like to do now, is to get a count of the results (how many True and False), summarized or filtered by a distinct category rather than each record... following the advice from https://community.powerbi.com/t5/Desktop/Is-it-possible-to-count-the-result-of-a-measure/m-p/520128#... i can get the counts for each record, but I can't figure out how to filter or summarize to get the counts by category...
Here's a simple example of my data and desired results...
Data
Category | Type | Transaction ID | Price/lb | Greater than $1 |
Apple | Green | 1 | $1.25 | TRUE |
Apple | Green | 2 | $1.35 | TRUE |
Apple | Green | 3 | $1.10 | TRUE |
Apple | Green | 4 | $1.40 | TRUE |
Apple | Red | 5 | $0.98 | FALSE |
Apple | Red | 6 | $1.10 | TRUE |
Apple | Red | 7 | $0.85 | FALSE |
Apple | Red | 8 | $0.99 | FALSE |
Orange | Tangerine | 9 | $1.35 | TRUE |
Orange | Tangerine | 10 | $0.95 | FALSE |
Current measure evaluates Median by category: =Median[Price/lb] resulting effectively in something like this in my pivot table...
Category | Median $ | Greater Than $1 |
Apple | $1.10 | TRUE |
Orange | $1.15 | TRUE |
The Desired Result is a table like this based on the category results above:
Results | Count |
TRUE | 2 |
FALSE | 0 |
so if my measure to calculate True or False is called [dollar] and my table is called 'Fruit', i feel like i should be able to use some combination of Filter, distinct, countrows or summarize, but so far I'm failing to find the right combination.
maybe something like =FILTER(DISTINCT('Fruit'[Category],COUNTROWS('Fruit'[dollar]=TRUE)))?
That doesn't work, but you get the idea... Can anyone point me in the right direction?
Thanks!
Hi Joe,
It looks like you want to use TRUE/FALSE as labels and this is probably not going to work with a measure, since a measure produces a scalar value.
You could create a calculated column in the table that assigns TRUE/FALSE to each row, and use that column as labels in your table.
After that you can create a simple measure like COUNTROWS(Fruit) and place it in the table.
Let me know what you think about it.
Regards,
Adrian
User | Count |
---|---|
57 | |
21 | |
19 | |
16 | |
16 |
User | Count |
---|---|
86 | |
77 | |
52 | |
37 | |
22 |