DAX - Column content based on slicer selection

[Anonymous]

Hi folks,

I've done some research on the forum and while some responses get close to what I need I can't quite find something that I can use.

I have a table which contains, amongst many fields, the following:

ITEM #      REQ CREATION DATE     PO CREATION DATE     RECEIVED DATE     REFERENCE DATE

I would like to have a filter to select either REQ CREATION DATE or PO CREATION DATE. If I select REQ CREATION DATE, then the column REFERENCE DATE needs to show the value across in REQ CREATION DATE. If I select PO CREATION DATE, the REFERENCE DATE should display the value across PO CREATION DATE.

I feel like this is not rocket science but cannot for the like of me do it 😄

Thanks in advance to anyone who can help.

OF

[MFelix]

Hi @Anonymous,

When you say that you need to have one date or the other is to have calculations made to show in a visual?

What is the final outcome you want? Because based on that can be different options.

However one of the options is to create a table with two lines with:

REQ CREATION DATE

PO CREATION DATE

Then add the following measure to your model:

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

This can then be used in another measures or visuals:

Regards,

MFelix

[Anonymous]

Hi MFeliz,

I will test this as soon as I’m back at work tomorrow 🙂 to answer your question, I would like to reference the value in REFERENCE DATE in several formulas and have a result dependant upon the filter.

Do you think that this solution will enable this?

Thanks,
OF

[MFelix]

Hi @Anonymous,

If you reference to this measure on the other formulas this will give you the expected result.

But be aware that using a measure is based on context so some of the formulas are not just simply using the measure you need to use aggregators or some filters in the new measures.

Regards,

MFelix

[Anonymous]

Hi MFelix,

First off, thank you for responding and helping me.

I followed your instruction to the letter but unfortunately the filter fails to display the correct value and more importantly, the calculated measure returns a date that doesn't exist in any of the fields.

Unfortunately I am not able to share the PBI file due to sensitive data but let me know if there is anything I can provide that could help with troubleshooting.

Thanks,

OZ

[MFelix]

Hi @Anonymous,

Can you share the measure you created?

Regards,
MFelix

[Anonymous]

Here it is:

DATE_REFERENCE_CREATION =
SWITCH(
     TRUE(),
     MAX('Slicer Table'[CREATION DATE])="REQUISITION",MAX('Receipts & Overdues'[CREATION_DATE_REQ]),
     MAX('Slicer Table'[CREATION DATE])="PURCHASE ORDER", MAX('Receipts & Overdues'[CREATION_DATE_PO]),
     MAX ('Receipts & Overdues'[CREATION_DATE_REQ])
     )

[MFelix]

Hi @Anonymous,

Do you have any Calendar Table or something similar connecting to you data table?

Regards,

MFelix

[Anonymous]

No nothing at all, only one SQL and its table loaded in the model

[MFelix · Accepted solution]

Hi @Anonymous,

Did you had a measure or a calculated column?

I'm assuming that since your data is always the same in the sample you showned that you created a calculated column, this will give you the maximum value of the date column in your data.

Below you can see the same measure as you have but in a calculated colum (COLUMN_REF) and in a measure (MEASURE_REF) the only one that changes accordingly to the slicer is the measure.

You need to change your formula to a measure instead of the column.

Regards,

MFelix

[forti4040]

Hello @MFelix,

I hope you don't mind me picking this thread back up. I think your solution could work for an issue I'm having but I can't quite get it working. I am trying to create two columns (though using your method I'm creating two measures) to represent Start Dates and Finish Dates that can be used in the Gantt 1.14.3 visual. Below is an example of the Start Date measure I've created. The idea here being that I can slice what is used in the measure by region to only show Start Dates from the applicable region start date column. 

REFERENCE Start DATE = 
SWITCH (
    TRUE (),
    MIN( RegionSlice[Region] ) = "ANZ", MIN (SQL_ProjectKPD[StartDate_ANZ]  ),
    MIN (RegionSlice[Region] ) = "NA", MIN (SQL_ProjectKPD[StartDate_NA]  ),
    MIN( RegionSlice[Region] ) = "LATAM", MIN (SQL_ProjectKPD[StartDate_LATAM]  ),
    MIN (RegionSlice[Region] ) = "Canada", MIN (SQL_ProjectKPD[StartDate_Canada]  ),
    MIN( RegionSlice[Region] ) = "EMEA", MIN (SQL_ProjectKPD[StartDate_EMEA]  ),
    MIN ( SQL_ProjectKPD[EarliestStart] )
)

The challenge I'm having is that I can't put a measure into the Start Date section of the Gantt 1.14.3 visual. I can put one into the End Date, but not the Start Date. I don't know if I'm doing something wrong, or if that's a limitation with the visual and I need to somehow find a solution that does not include using a measure in the Start Date values. 

Any help would be appreciated

Regards,

Eric

[MFelix]

Hi @forti4040,

Without any data sample is difficult to give you an exact answer but looking at your measure I can see you have several columns for the start date, and assuming also for the end date, why don't you unpivot the columns tom only have 3 Start Date , End Date , Region (ANZ, NA, LATAM, ...).

If you have your model setup like this you can the use the region to slice your date and you gannt chart.

Can you provide a sample or a PBIX file? if you have the need to keep data private share it trhough private message or send a mockup data.

Regards,

MFelix

[forti4040]

@MFelix,

Thank you for the guidance. I was able to do some unpivoting and merging to get to the desired end result. Very much appreciate your help!

Regards,

Eric

[Anonymous]

Hi I am just opening the thread again. I am trying to do something similar.

I have created a slicer table with values - YearToDate and Rolling12Months.

My Main data table (which is unrelated to Slicer table) has data for past 24 months. In thsi table, I would like to calculate a column which gives values as - Current Year and Previous Year, based on the date and the slicer selected.

So if I select YearTodate as Slicer my calculated column should have values like

Date                 Calculated Column
01/01/2020      Current Year

02/01/2020      Current Year

01/01/2019      Previous Year

02/01/2019      Previous Year

03/01/2019      Null

04/01/2019      Null

.

.

.

and so on.

Can you suggest how can I achieve this. I have tried SelectedVlues, but it soes not help.

[MFelix]

H @Anonymous ,

You cannot make calculated columns based on slicers selection because slicers are used to filter out information from a dataset so you are refering to the data itself again.

What is you main objective with this column is to calculate the Current Year values and the previous years?

This can be achieve by the use of a table of dates and a use of filters in the measures, something similar to:

PY Value = CALCULATE (SUM(Table[Column]); DATEADD(DateTable[Date]; -1 ; YEAR))

This is just an example but if you want to have more intricate calculations based on dates selected or quaters that is also possible.

If you can explain and give some data example I can try and help you to achieve the response you need.

[Anonymous]

You are a very clever cookie 🙂 Thank you it works perfect.

You did make a comment earlier around implications in subsequent calculations Can you think of any gaps where I calculate the following:

. difference (using DATEDIFF) between a date (from an existing column) and the new MEASURE REF?

. have a measure calculating PERCENTILE.INC (MEASURE REF, 0.9) to extract the 90th percentile of the population

Thanks again for your help!

OF

Edit: what did you use to record the gif you posted?

[MFelix]

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