cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joebc Frequent Visitor
Frequent Visitor

summarizing the results of a measure

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

CategoryTypeTransaction IDPrice/lbGreater than $1
AppleGreen1$1.25TRUE
AppleGreen2$1.35TRUE
AppleGreen3$1.10TRUE
AppleGreen4$1.40TRUE
AppleRed5$0.98FALSE
AppleRed6$1.10TRUE
AppleRed7$0.85FALSE
AppleRed8$0.99FALSE
OrangeTangerine9$1.35TRUE
OrangeTangerine10$0.95FALSE

 

Current measure evaluates Median by category: =Median[Price/lb] resulting effectively in something like this in my pivot table...

 

CategoryMedian $Greater Than $1
Apple$1.10TRUE
Orange$1.15TRUE

 

The Desired Result is a table like this based on the category results above:

ResultsCount
TRUE2
FALSE0

 

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!

 

1 REPLY 1

Re: summarizing the results of a measure

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.

Capture.PNG

Let me know what you think about it.

 

Regards,

 

Adrian

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 99 members 1,482 guests
Please welcome our newest community members: