Forum Discussion
DAX - Column content based on slicer selection
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
Super UserHi 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
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 Anonymous,
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
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
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 Year02/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.