Filter by a measure

[pceglie]

Hi folks, I have one table with 2 measures

"Total Revenues" , "Total Budget"

I have a 3rd measure that prints "OK" if the first 2 columns have the same value otherwise prints "KO" if Total Revenues" <> "Total Budget". 

Works fine, but I can't insert a Slicer Filter to filter only rows with "OK" (or "KO") values

Any suggestion is appreciated.

Thanks,

[MattAllington]

You can't put a measure as a slicer choice. You could try adding a bar chart with the 2 text items on the axis and count as the value. Then try clicking the bars

[pceglie]

Hi, thanks for your reply

"You could try adding a bar chart with the 2 text items on the axis and count as the value. Then try clicking the bars"

What do you mean with "with the 2 text items on the axis"?

I'm not able to add the 3rd measure as AXIS in a bar chart

[Greg_Deckler]

Could you supply some sample data (as text, not screen shot) and your measure calculations so that the problem can be recreated? See this post:

http://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[pceglie]

For testing I use sample database WorldWideImportersDW

and below my measures

Purchased = CALCULATE(SUM('Fact Purchase'[Ordered Quantity]))

Sales = CALCULATE(SUM('Fact Sale'[Quantity]))

_meas = IF([Purchased]=[Sales];"OK";"KO")

So I need filtering only rows "OK" or "KO"

[Greg_Deckler]

Can you just use a Visual Filter, Page Filter or Report Filter?

[pceglie]

I need a Visual Filter

[v-sihou-msft · Accepted solution]

@pceglie

We can't directly put a measure into a slicer since the measure need to be sliced to get the corresponding data. In your scenario, you can create a calculated table and build those measures into calculated column, then you can use apply slicers on these columns.

Calculated Table = ADDCOLUMNS(
Table,
"Purchased",CALCULATE(SUM('Fact Purchase'[Ordered Quantity])),
"Sales",CALCULATE(SUM('Fact Sale'[Quantity])),
"_meas", IF([Purchased]=[Sales];"OK";"KO")
)

Regards,

Simon Hou

[drad]

How do you build this table? Where do you write the script you supplied?

Is there any documentation you can supply to show how this is done?

Thanks,

Dan.

[Greg_Deckler]

I may be incorrect, but I'm not sure that is possible. The reason is that since it is a measure, it has a value in a visual only according to the specific context of that visual. Filters are special in that they allow measures to be used to filter visuals versus all of the visuals aren't going to allow you to do this in the way you want. There may be a tricky way around it but I'm not thinking of it right now. I seem to remember that perhaps there was a way around this or a similar problem by using VALUES...