deactivate slices with ALL function

[Anonymous]

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

[v-henryk-mstf · Accepted solution]

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]

@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]

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