Forum Discussion
Distinct Date count as a slicer
I have a date column in my table. I derived month & day from the date column. No of days service operated/Service run days is -count of distinct date. In my data (april, june & july month data is there).For example in the data servces A,B,C,D...Z are there. A has run overall of 67 days, B-81 days,C-29 days, D-52 days(52 distinct date)....Z-90 days. Now I want to create a slicer which should give me the values range in the slicer but not the distinct count range in the slicer.
Also if month slicer is enabled for example july is selected and in the range I selected 1 to 20 then in my output services ran less than or equal to 20 in july month should come.
The problem I am facing is it is caluclating 20 as a date not as a count. Someone please help me with this
Hi Anonymous ,
One way to create a numeric range slicer based on a measure is to use the DAX function SWITCH to create a calculated column that assigns a range label to each row based on the count of distinct dates2. For example, you can create a column called Range with a formula like this:
Range = VAR ranges = [Count of Distinct Date] RETURN SWITCH ( TRUE (), ranges <= 20, "From 1 to 20", ranges <= 40, "From 21 to 40", ranges <= 60, "From 41 to 60", ranges <= 80, "From 61 to 80", ranges <= 90, "From 81 to 90" )This column will create a range label for each row based on the count of distinct dates. You can then use this column as a slicer and filter your data by the range labels.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
One way to create a numeric range slicer based on a measure is to use the DAX function SWITCH to create a calculated column that assigns a range label to each row based on the count of distinct dates2. For example, you can create a column called Range with a formula like this:
Range = VAR ranges = [Count of Distinct Date] RETURN SWITCH ( TRUE (), ranges <= 20, "From 1 to 20", ranges <= 40, "From 21 to 40", ranges <= 60, "From 41 to 60", ranges <= 80, "From 61 to 80", ranges <= 90, "From 81 to 90" )This column will create a range label for each row based on the count of distinct dates. You can then use this column as a slicer and filter your data by the range labels.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.