Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to Count Slicer Items

Hello Supporting Community,

Here is the Dropbo File
https://www.dropbox.com/s/l90npr8j6xllhcn/Employe_Count.pbix?dl=0

I am need to count the Employs, if from Category slicer any item is selected.
Currently "Admin" is selected and during the selected date range there are 5 Employs. So I have to display 5 as a straight line on my Line graph. but it is displaying trend instead of straight line.

While in other measure I used RemoveFilter "Date" and it is displaying 6. but it is getting one employ who is out side of the selected date range which is correct.

but I need 5 as a straight line.



If there is any way please suggest.

Thanks in Advance

COUNT
Filter
Slicer

2 Replies

  • Michael_Hall's avatar
    Michael_Hall
    Regular Visitor
    3 years ago

    There is a simple solution.  You are not wanting your measure to be sliced by the dates in the visual. Therefore, just create a new calendar that is disassociated from everything and use its dates in the visual.

    1. Modeling > New table

     

    Calendar = CALENDAR(DATE(2019,1,1), DATE(2025,12,31))

     

    2. Replace the date in the x-axis of your visual with this one.

    3. You can limit the range of the x-axis to your slicer's range using visualizations > format visual > range > minimum/maximum

     

    Now you have a chart with two straight lines (6 for total and 5 for emp_Count).

     

    I assume you want some other data in the chart. You can make measures to refer to your new calendar. For example:

     

    Emp_Count for date = 
VAR the_date = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(DISTINCTCOUNT(EmployCount[Employ]),
          EmployCount[Date_Range] = the_date)

     


    Or equivalently:

     

    Emp_Count for date = 
CALCULATE(DISTINCTCOUNT(EmployCount[Employ]),
          TREATAS(VALUES('Calendar'[Date]), EmployCount[Date_Range]))

     

     

    Result: