Forum Discussion

TonyGu's avatar
TonyGu
Helper I
3 years ago

Filtering a DAX Measure with another DAX Measure

Hey, I was wondering if anyone could help. I have a table with billings data as such 

 

Invoice ID | Store ID | Transaction Date | Amount 

 

Connected to a DimStore table ie. 

 

Store ID | Store Name | 

 

 

Sales YOY = VAR __PREV_YEAR = [Sales PY]
RETURN
    DIVIDE([Sales] - __PREV_YEAR, __PREV_YEAR)

 

Where 

Sales PY = CALCULATE([Sales], SAMEPERIODLASTYEAR('DimDates'[Date]))
Sales = SUM('Billings'[Amount])
 
 
With one store selected, I want to see the total YOY for all stores. Which I can do as such,
Sales YOY All Store = CALCULATE([Sales YOY],REMOVEFILTERS(DimStore[Store Name]))
 
This works well, but I would like to filter out stores that have a YOY of more than 50%. I tried approaching this with a virtual table, but since Sales YOY is a measure, I can't filter that out since YOY is a DAX Measure and not a column.
 
 
Any idea how we could filter out DAX Results of a SELECTEDVALUE of DimStore?
Need Help
Show and Tell

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    I do not understand the context.  If you select one store then why do you want to see the Y-o-Y of all stores?  Share some data to work with and show the expected result.

    • TonyGu's avatar
      TonyGu
      Helper I
      3 years ago

      Hi, 

       

      I would like to see how the selected store did in comparison to all the stores. E.g. Store 1 had 5% YoY growth. However, all the stores had about 15% growth. As such, the 5% growth is an underperformance. There is a Store name slicer which would allow me to select a single store and the All store measure will stay intact so I can see the variance between the selected store and all stores. 

       

      eg. 

      |Invoice ID | Store ID | Transaction Date | Amount 

      1|1| 1st June 2021| 500

      2|1|1st June 2022 | 650

      3|2|1st June 2021 | 800

      3|2| 1st June 2022 | 900

      4|3| 1st June 2021 | 500

      5|3| 1st June 2022 | 1500

       

      With Store 1 selected, 

       

      YOY = 30% (650-500/500)

      All Store = 70% (3050-1800/1800)

       

      Desired Measure would filter out Store ID 3 as the store has YOY of 200%. However, Store 1 and Store 2 would remain as their YOY is less than 50%

       

      As such, desired measure would calculated 1550-1300/1300= 19%

       

       

      I can't upload pbix, but here's the recreation. 

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWB9M30jAyMQ09TAQClWJ1rJCFUKxDUzhUgZA9lGqLosoLpMUKVATEuolCmQbYzdLjNUKbABYLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice ID" = _t, #"Store ID" = _t, #"Transaction Date" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}, {"Store ID", Int64.Type}, {"Amount", type number}, {"Invoice ID", Int64.Type}})
in
    #"Changed Type"

----------------------------------------------------------------------------

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouyS9KVTBUitWJVjKC843AfGM431gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, #"Store Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store ID", Int64.Type}})
in
    #"Changed Type"

       

       

      Sales = SUM('Billings Table'[Amount])
       
      Sales PY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Billings Table'[Transaction Date]))
       
      Sales YOY = VAR __PREV_YEAR = [Sales PY]
      RETURN
          DIVIDE([Sales] - __PREV_YEAR, __PREV_YEAR)
       
      Sales YOY All Store = CALCULATE([Sales YOY],REMOVEFILTERS(DimStore[Store Name]))