Forum Discussion

pceglie's avatar
pceglie
Resolver I
9 years ago
Solved

Filter by a measure

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...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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

pceglie's avatar
pceglie
Resolver I
9 years ago

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's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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

    • pceglie's avatar
      pceglie
      Resolver I
      9 years ago

      I need a Visual Filter

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        9 years ago

        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