Forum Discussion

lrochester's avatar
lrochester
Frequent Visitor
8 years ago

DAX - ALLSELECTED with Additional Column in Table

Hi

 

I have created a calculated column to give me the previous Sale Date and a calculated measure to calculate the avg TxnAmt.

 

I have several report slicers so have used ALLSELECTED but I also need to take a table column value (Supplier) into consideration.

 

Here's my DAX so far...

 

PrevSaleDate =
VAR CurrentSaleDate = 'Data'[SaleDate]
RETURN
CALCULATE(MAX('Data'[SaleDate]),
FILTER(ALLSELECTED('Data'),
'Data'[SaleDate] < CurrentSaleDate
)
)

 

PrevSaleDateAvgTxnAmt = CALCULATE(
AVERAGE( 'Data'[TxnAmt] ),
FILTER ( ALLSELECTED ( 'Data' ), MAX( 'Data'[PrevSaleDate] ) = 'Data'[SaleDate] )
)

 

 

 

Attached is a pbix with example data and the calculated column and measure - Example.pbix

 

I'm stuck at the last hurdle so any help would be greatly appreciated!

 

Thanks

Need Help

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi lrochester

     

    Check this one

     

    PrevSaleDate =
VAR CurrentSaleDate = 'Data'[SaleDate]
RETURN
    CALCULATE (
        MAX ( 'Data'[SaleDate] ),
        FILTER (
            ALLEXCEPT ( 'Data', Data[Supplier] ),
            'Data'[SaleDate] < CurrentSaleDate
        )
    )
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi lrochester

       

      PrevSaleDateAvgTxnAmt =
CALCULATE (
    AVERAGE ( 'Data'[TxnAmt] ),
    FILTER (
        ALLEXCEPT ( Data, Data[Supplier], Data[SaleType], Data[DiscountApplied] ),
        'Data'[SaleDate] = SELECTEDVALUE ( Data[PrevSaleDate] )
    )
)
    • lrochester's avatar
      lrochester
      Frequent Visitor
      8 years ago

      Thanks, Zubair.

       

      Neither are quite right though I'm afraid.

       

      I've add some more example data, applied your suggestions, and shown the expected results in this pbix.

      • lrochester's avatar
        lrochester
        Frequent Visitor
        8 years ago

        Forgot to add...

         

        In the actual report pbix, there will be quite a few more slicers than in the example (11 in total) so I originally opted for the ALLSELECTED function.

         

        Also, the row count of the data is going to be in the 100's of millions so I need the calculated column and measure to be super slick.

         

        Thanks again and I really appreciate your help :)