DAX - Column content based on slicer selection

Hi Anonymous,

Since you are using a measure and it's based on context if you change your visual filters / categories the measure will be recalculated.

Breaking down the measure it return the maximum date based on the full data table in this case when you add the PO/Release you are adding context and getting all the rows and for each row it's giving the maximum date .

In the example below you can see that I have made two different measures for date column and two other for difference in dates. A SWITCH as you have and another changing the MAX to MIN as you can see the total line in the DATEDIFF measure is giving you the smallest values when you use MAX (gets the PO date from Item A 20/01/2018) and the biggest value when you use MIN (gets PO date from item C 20/01/2018).

Based on this if you add a card or a graph based on month you will get the incorrect total since it's getting only the maximum value on this case you need to add an aggregator like SUMX to have the calculation made row by row for each item and then sum it up, in this case it will be indiferent to have the MAX or MIN selection on your SWITCH formula, as you can see the line results are the same but the total is different since it's adding up all the values:

(16+11+23+22) = 72

As you can see based on the context it gives you different results, so you have to be carefull with your calculations, and use the aggregators to have the correct results.

Measures used in the example:

MAXIMUM REFERENCE

REFERENCE DATE (MAX) = 
SWITCH (
    TRUE ();
    MAX ( Slicer_table[Slicer] ) = "REQ"; MAX ('DataTable'[REQ CREATION DATE]  );
    MAX ( Slicer_table[Slicer] ) = "PO"; MAX ( 'DataTable'[PO CREATION DATE] );
    MAX ( 'DataTable'[REQ CREATION DATE] )
)

Date_Diff (MAX) = DATEDIFF([REFERENCE DATE (MAX)];MAX('DataTable'[RECEIVE DATE]);DAY)

DATE_DIFF SUMX (MAX) = CALCULATE(SUMX('DataTable'; [Date_Diff (MAX)]))


MINIMUM REFERENCE 

REFERENCE DATE (MIN) = 
SWITCH (
    TRUE ();
    MAX ( Slicer_table[Slicer] ) = "REQ"; MIN ('DataTable'[REQ CREATION DATE]  );
    MAX ( Slicer_table[Slicer] ) = "PO"; MIN ( 'DataTable'[PO CREATION DATE] );
    MIN( ( 'DataTable'[REQ CREATION DATE] )
))


Date_Diff (MIN) = DATEDIFF([REFERENCE DATE (MIN)];MIN('DataTable'[RECEIVE DATE]);DAY)

DATE_DIFF SUMX (MIN) = CALCULATE(SUMX('DataTable'; [Date_Diff (MIN)]))

Be also aware that I have made the SUMX based on the full table because I don't have any big data but if you table is large you should make something like:

DATE_DIFF SUMX (MIN) =
CALCULATE (
    SUMX (
        ALL ( 'DataTable'[Column1]; 'DataTable'[Column3]; 'DataTable'[Column3] );
        [Date_Diff (MIN)]
    )
)

Replace Column 1/2/3 by all the columsn you need to make context on your data, this will improve performance.

Hope this explanations helps.

I have used the ScreenToGif, just select the area record and save it, then upload as a normal photo.

Regards,

MFelix