Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

deactivate slices with ALL function

Hello Community,

 

I need you help with the ALL function.

 

I built a table showing the ranking of my data extraction dates.

 

Based on this ranking, I then calculate the differences between the newest extraction versus the previous one.

 

Diff-1_Pallets =
VAR LatestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = 1))
VAR PreviousExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(Shipments,Shipments[Index] = 2))
Return
LatestExtraction - PreviousExtraction
 
In order to generate correct results,
 
  1. I then create a slicer , based on the Extraction Date column of my fact table (Shipments table) , and
  2. deactivate the relationship between this slicer and the visual showing this difference
 
I am wondering if there is a way to rewrite the measure in such a way that will not require the slicer's interaction.
 
I visited Microsoft's page to read more about ALL. I came across the below explanation.
 

 

Thus, I wrote the below measure.

 

Diff-1_Pallets_All =
VAR LatestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(ALL(Shipments,Shipments[Extraction Date]),Shipments[Index] = 1))
VAR PreviousExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(ALL(Shipments,Shipments[Extraction Date]),Shipments[Index] = 2))
Return
LatestExtraction - PreviousExtraction
 
, only to receive the below error.
 

Can you please advise what I am missing here?

 

Kind regards,

 

George

 
 
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi Anonymous ,

     

    According to your description, do you want to show the results of the second screenshot without filtering the date as well? Why not try the ALLSELECTED function, which returns all values in all rows or columns of a table, ignoring any filters that may be applied inside the query and keeping only those from outside.

     

    check the below video may wish to help you understand related function

    ALL Vs ALLSELECTED Vs ALLEXCEPT DAX FIiter Functions | DAX Sundays Ep 5 - YouTube

     

    Since I can't know your data model, I can't do any further testing. If the problem persists, can you provide the relevant test data (with sensitive information removed). I will answer for you as soon as possible.

     

    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Anonymous ,

     

    According to your description, do you want to show the results of the second screenshot without filtering the date as well? Why not try the ALLSELECTED function, which returns all values in all rows or columns of a table, ignoring any filters that may be applied inside the query and keeping only those from outside.

     

    check the below video may wish to help you understand related function

    ALL Vs ALLSELECTED Vs ALLEXCEPT DAX FIiter Functions | DAX Sundays Ep 5 - YouTube

     

    Since I can't know your data model, I can't do any further testing. If the problem persists, can you provide the relevant test data (with sensitive information removed). I will answer for you as soon as possible.

     

    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try a measure  like

     

    Diff-1_Pallets_All =
    VAR LatestExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(ALL(Shipments) , Shipments[Index] = 1))
    VAR PreviousExtraction = CALCULATE(SUM(Shipments[Pallet_Num]),FILTER(ALL(Shipments) ,Shipments[Index] = 2))
    Return
    LatestExtraction - PreviousExtraction

     

    Or use index in all not date

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello amitchandak ,

       

      Thank you for your reply.

       

      I created the new measures as suggested.

       

       

       

      Unfortunately, it didn't return the desired result.

       

      ALL Visual

       

      Initial Visual (correct results)

       

       

      In case my initial post was not clear enough, I want to allow the interaction of all potential filters towards the Shipment table , apart from the extraction date.

       

      The desired behaviour of the above visual is to always sum the quantity of the pallets of the most recent extraction (index = 1) and then calculate the differences versus the previous extraction (index = 2) and then, the one before that (index = 3).

       

      Thank you,

       

      George