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 Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi lrochester.

 

>>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.

Allselceted suitbale for your requirement, you need to double check on relationships to confirm you have modified 'cross fitler direction' option to 'both'. Otherwise the filter effect only works on one side.

 

Regards,

Xiaoxin Sheng

lrochester's avatar
lrochester
Frequent Visitor
8 years ago

Hi Xiaoxin

 

All of the columns I need are in one table so there's no need to join in this instance :)

 

Would you be able to send me the DAX I should use for PrevSaleDate and PrevSaleDateAvgTxnAmt please? 

 

Thanks

Lucy

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi lrochester,

     

    Perhaps you can try to use below measure if it suitable for your requirement:

    PrevSaleDateAvgTxnAmt =
VAR previousDate =
    MAXX (
        FILTER ( ALLSELECTED ( 'Data' ), 'Data'[SaleDate] < MAX ( 'Data'[SaleDate] ) ),
        [SaleDate]
    )
RETURN
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Data' ), previousDate = 'Data'[SaleDate] ),
        [TxnAmt]
    )

    Regards,

    Xiaoxin Sheng