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
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
        )
    )
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 :)

    • 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