cancel
Showing results 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

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

 clients App date Start Date stop date 1 1/1/2021 2/1/2021 6/1/2021 2 1/1/2022 1/16/2022 1/16/2023 3 1/1/2023 2/1/2023 1/31/2025 4 1/1/2022 3/4/2022 6/2/2022 5 1/1/2022 4/1/2022 5/31/2022 6 1/1/2023 1/21/2023 4/26/2023 7 1/1/2023 5/1/2023 8 1/1/2024 6/29/2024 9 1/1/2023 1/16/2023 3/17/2023 10 1/1/2022 1/1/2023 3/17/2023 11 1/1/2021 6/30/2021 12 1/1/2022 4/1/2022 7/10/2022 13 1/1/2024 2/1/2021 3/18/2021 14 1/1/2023 1/16/2023 4/16/2023 15 1/1/2024 2/1/2024 5/31/2024 16 1/1/2022 3/4/2022 3/4/2023 17 1/1/2022 4/1/2022 3/31/2024 18 1/1/2020 1/21/2020 3/21/2020 19 1/1/2022 5/1/2022 8/4/2022 20 1/1/2023 21 1/1/2023 1/16/2023 3/26/2025 22 1/1/2023 1/1/2024 1/31/2024 23 1/1/2023 24 1/1/2023 4/1/2023 6/30/2023

1 ACCEPTED SOLUTION
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:

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.

2 REPLIES 2
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:

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.

Helper I

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.

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors