Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BW40
Helper I
Helper I

How can I get three separate counts with one date slicer?

We have clients with three relevent dates (application, start, and stop). I've made a seperate date table so now the report has three matrices  (one for each count). How can I get a distinct count of clients if their date fell in the larger date range? i.e. I want a disinct count of clients who applied in the date range, a seperate distinct ount of clients who started in the range, and then a distinct count of clients who ended in the range. 

 

In a formula it would be IF([app date] >= [period start] & [app date] <= [period end], 1, 0) and so on but that requires me to make two seperate date columns that I change for the viewers. I need three counts, using different dates, filtered by one slicer my viewers change. If they occured in the timeframe it is  a 1 otherwise a 0.

 

I created some eample data below

clientsApp dateStart Datestop date
11/1/20212/1/20216/1/2021
21/1/20221/16/20221/16/2023
31/1/20232/1/20231/31/2025
41/1/20223/4/20226/2/2022
51/1/20224/1/20225/31/2022
61/1/20231/21/20234/26/2023
71/1/20235/1/2023 
81/1/20246/29/2024 
91/1/20231/16/20233/17/2023
101/1/20221/1/20233/17/2023
111/1/20216/30/2021 
121/1/20224/1/20227/10/2022
131/1/20242/1/20213/18/2021
141/1/20231/16/20234/16/2023
151/1/20242/1/20245/31/2024
161/1/20223/4/20223/4/2023
171/1/20224/1/20223/31/2024
181/1/20201/21/20203/21/2020
191/1/20225/1/20228/4/2022
201/1/2023  
211/1/20231/16/20233/26/2025
221/1/20231/1/20241/31/2024
231/1/2023  
241/1/20234/1/20236/30/2023

 

 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @BW40 ,

Please try to create a date table for slicer visual:

Table 2 = CALENDAR(dt"2023-01-01",dt"2023-12-01")

Create a measure with below dax formula:

Measure =
VAR _min =
    MIN ( 'Table 2'[Date] )
VAR _max =
    MAX ( 'Table 2'[Date] )
VAR tmp =
    CALENDAR ( _min, _max )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [App date] IN tmp )
VAR tmp2 =
    FILTER ( ALL ( 'Table' ), [Start Date] IN tmp )
VAR tmp3 =
    FILTER ( ALL ( 'Table' ), [stop date] IN tmp )
VAR _a =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp1 )
VAR _b =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp2 )
VAR _c =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp3 )
RETURN
    "Count 1:" & _a & "
" & "Count 2:" & _b & "
" & "Count 3:" & _c

 

Add a slicer visual and card visual:

Animation40.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-binbinyu-msft
Community Support
Community Support

Hi @BW40 ,

Please try to create a date table for slicer visual:

Table 2 = CALENDAR(dt"2023-01-01",dt"2023-12-01")

Create a measure with below dax formula:

Measure =
VAR _min =
    MIN ( 'Table 2'[Date] )
VAR _max =
    MAX ( 'Table 2'[Date] )
VAR tmp =
    CALENDAR ( _min, _max )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [App date] IN tmp )
VAR tmp2 =
    FILTER ( ALL ( 'Table' ), [Start Date] IN tmp )
VAR tmp3 =
    FILTER ( ALL ( 'Table' ), [stop date] IN tmp )
VAR _a =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp1 )
VAR _b =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp2 )
VAR _c =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp3 )
RETURN
    "Count 1:" & _a & "
" & "Count 2:" & _b & "
" & "Count 3:" & _c

 

Add a slicer visual and card visual:

Animation40.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Took a little adjusting but that appears to have worked. I needed to break it down by further who worked with the clients and the employees' supervisors so I dropped the "ALL" part of the DAX so get the specific counts rather than only the totals.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.