Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to generate a customer slicer that can be used to toggle between date columns to generate a time sensitive chart of counts per day based on the selected date field from the slicer.
I created the customer slicer using a measure table with the fully qualified date field (i.e., table_name[date_selection]), but am having problems tying the slicer selection to the date column in the chart using the measure date column. I would love help on the path forward using this approach or any suggestions for an alternative approach that might be better. Any help you can provide would be greatly appreciated. Thank you!
first date | last date | recent date | product code |
6/20/2018 | 11/9/2018 | 10/10/2018 | 1111 |
6/20/2018 | 11/9/2018 | 11/9/2018 | 1111 |
4/1/2018 | 10/30/2018 | 11/2/2018 | 1121 |
6/20/2018 | 6/30/2018 | 11/10/2018 | 1121 |
10/30/2018 | 11/2/2018 | 11/7/2018 | 1141 |
6/20/2018 | 11/9/2018 | 11/9/2018 | 1141 |
4/1/2018 | 11/9/2018 | 11/9/2018 | 1141 |
10/30/2018 | 11/3/2018 | 11/9/2018 | 1141 |
5/20/2018 | 6/20/2018 | 8/1/2018 | 1151 |
Slicer
First Date Last Date Recent Date
Solved! Go to Solution.
Hi @Anonymous,
Add a calendar table and a table for slicer selection with the following data:
Slicer Selection
first date |
last date |
recent date |
Then add the following measure:
Date selection = SWITCH ( MAX ( slicers[Slicer Selection] ); "first date"; CALCULATE ( COUNT(Table1[product code]); FILTER ( ALL ( Table1[first date] ); Table1[first date] = MAX ( 'Calendar'[Date] ) ) ); "last date"; CALCULATE ( COUNT(Table1[product code]); FILTER ( ALL ( Table1[last date] ); Table1[last date] = MAX ( 'Calendar'[Date] ) ) ); "recent date"; CALCULATE ( COUNT(Table1[product code]); FILTER ( ALL ( Table1[recent date] ); Table1[recent date] = MAX ( 'Calendar'[Date] ) ) ) )
Replace the
Count( Table1[product code] )
By the other measure you want to calculate sum, max, min, whatever then just add the slicer of the calendar table and another one for the slicer table and should work.
see attach PBIX file
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous,
Add a calendar table and a table for slicer selection with the following data:
Slicer Selection
first date |
last date |
recent date |
Then add the following measure:
Date selection = SWITCH ( MAX ( slicers[Slicer Selection] ); "first date"; CALCULATE ( COUNT(Table1[product code]); FILTER ( ALL ( Table1[first date] ); Table1[first date] = MAX ( 'Calendar'[Date] ) ) ); "last date"; CALCULATE ( COUNT(Table1[product code]); FILTER ( ALL ( Table1[last date] ); Table1[last date] = MAX ( 'Calendar'[Date] ) ) ); "recent date"; CALCULATE ( COUNT(Table1[product code]); FILTER ( ALL ( Table1[recent date] ); Table1[recent date] = MAX ( 'Calendar'[Date] ) ) ) )
Replace the
Count( Table1[product code] )
By the other measure you want to calculate sum, max, min, whatever then just add the slicer of the calendar table and another one for the slicer table and should work.
see attach PBIX file
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix,
It took me a little time to get it implemented and fixed. Your solution is golden. Thank you Soooo much for your assistance. I was close, but you got me straight and your solution worked like a dream. I truely appreciate it!!
ML
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |