Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help with matrix DAX

Hello, I have the following slicers set up in my dashboard:

 

  1. Date
  2. Fruit Category
  3. Fruit Name
  4. Fruit Color
  5. Fruit Sweetness Level

 

And I have the following formula that gives me the dynamic MoM% calculation when any two dates in my date slicer is selected:

 

FruitPurchases =
VAR EndMonthYear =
    MAXX ( ALLSELECTED ( 'Table_Month' ), 'Table_Month'[Number] )
VAR StartMonthYear =
    MAXX ( ALLSELECTED ( 'Table_Month' ), 'Table_Month'[Number] ) - 1
VAR StartSpend =
    CALCULATE ( [sumfruitprices], KEEPFILTERS('Table_Month'[Number] = StartMonthYear ))
VAR EndSpend =
    CALCULATE ( [sumfruitprices], KEEPFILTERS('Table_Month'[Number] = EndMonthYear ))
RETURN
    IF (
        NOT ISBLANK ( StartSpend ) && NOT ISBLANK ( EndSpend ),
        DIVIDE ( EndSpend - StartSpend, StartSpend),"N/A")

 

My issue is, everytime more than 2 slicers are selected, my calculation goes blank. I'm getting the right numbers, however for some reason my calculation won't work when more than 2 slicers are selected. For example, if I chose a two values in the Date slicer, then I chose a value in the Fruit Category slicer, I get the correct result. But if I then try to also choose a value in the Fruit Name slicer, the result shows "N/A".

 

Could you please help me figure this out?

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Anonymous Try using

    DAX
    FruitPurchases =
    VAR EndMonthYear =
    MAXX ( ALLSELECTED ( 'Table_Month'[Number] ), 'Table_Month'[Number] )
    VAR StartMonthYear =
    MAXX ( ALLSELECTED ( 'Table_Month'[Number] ), 'Table_Month'[Number] ) - 1
    VAR StartSpend =
    CALCULATE ( [sumfruitprices], REMOVEFILTERS('Table_Month'), 'Table_Month'[Number] = StartMonthYear )
    VAR EndSpend =
    CALCULATE ( [sumfruitprices], REMOVEFILTERS('Table_Month'), 'Table_Month'[Number] = EndMonthYear )
    RETURN
    IF (
    NOT ISBLANK ( StartSpend ) && NOT ISBLANK ( EndSpend ),
    DIVIDE ( EndSpend - StartSpend, StartSpend),
    "N/A"
    )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Anonymous Try using

    DAX
    FruitPurchases =
    VAR EndMonthYear =
    MAXX ( ALLSELECTED ( 'Table_Month'[Number] ), 'Table_Month'[Number] )
    VAR StartMonthYear =
    MAXX ( ALLSELECTED ( 'Table_Month'[Number] ), 'Table_Month'[Number] ) - 1
    VAR StartSpend =
    CALCULATE ( [sumfruitprices], REMOVEFILTERS('Table_Month'), 'Table_Month'[Number] = StartMonthYear )
    VAR EndSpend =
    CALCULATE ( [sumfruitprices], REMOVEFILTERS('Table_Month'), 'Table_Month'[Number] = EndMonthYear )
    RETURN
    IF (
    NOT ISBLANK ( StartSpend ) && NOT ISBLANK ( EndSpend ),
    DIVIDE ( EndSpend - StartSpend, StartSpend),
    "N/A"
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      This worked like a charm. Thank you so much

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

    Hey Anonymous ,

     

    Please provide a screenshot of your semantic model.

    I assume that all your "Fruit ..." slicers are coming from the same table; I'm not sure if it's a dedicated "fruit" table. Hence, I'm asking for a screenshot of your semantic model.

     

    Regards,

    Tom