topic Re: Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer in DAX Commands and Tips

Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer

MITHUN_NAIR — Tue, 31 Aug 2021 07:38:20 GMT

Hi All,

Need your help in Solving the Below Issue.

if I Select a Date from the Slicer(Report Levvel) i.e. 28/08/2021 I should get the Count of Distinct Clients based on the below Condition.

KPI=Not met Or Quality=Not Met .

It should compare the above Condition for three consecutive days i.e 26th ,27th & 28th
if any of this column (KPI & Quality) is Not Met for all 3 rows for each client then find the Distinct Count of Clients from the Above Data. The Result would be 1(Distinct Count of Clients-A).
Only take the count if all the 3 rows satisfies the condition

I have Created a Measure for the Specific Date that is 28th July

DAX Code: Measure

Counts =
VAR Selected_Date =
SELECTEDVALUE ( Date[Date] )
VAR CountofClients =
CALCULATE (
DISTINCTCOUNT ( Clients[Client] ),
FILTER ( Clients, Clients[KPI] = "Not Met" || Clients[Quality] = "Not Met" ),
Clients[Date] = Selected_Date
)
RETURN
CountofClients

Thanks.

Re: Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer

Tanushree_Kapse — Tue, 31 Aug 2021 05:27:43 GMT

Hi MITHUN_NAIR ,

Try the below measure:

VAR Selected_Date =SELECTEDVALUE ( Date[Date] )

Return CountofClients= CALCULATE( DISTINCTCOUNT( Clients[Client] ), DATESBETWEEN( 'Date'[Date], DATEADD('Date'[Date],-2, DAY), Selected_Date))

Mark this as a solution if I answered your question. Kudos are always appreciated.

Thanks.

Re: Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer

MITHUN_NAIR — Tue, 31 Aug 2021 05:44:32 GMT

Hi Tanusree,

Thanks for your quick response.

From your code we are getting the data for those dates. But where are we comparing the values based on the condition I mentioned.Need to get those count where the condition satisfies

Re: Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer

Tanushree_Kapse — Tue, 31 Aug 2021 05:47:22 GMT

Hi MITHUN_NAIR ,

VAR Selected_Date =SELECTEDVALUE ( Date[Date] )

Return CountofClients= CALCULATE( DISTINCTCOUNT( Clients[Client] ), DATESBETWEEN( 'Date'[Date], DATEADD('Date'[Date],-2, DAY), Selected_Date) ,FILTER ( Clients, Clients[KPI] = "Not Met" || Clients[Quality] = "Not Met" ))

Re: Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer

MITHUN_NAIR — Tue, 31 Aug 2021 06:30:22 GMT

Hi Tanusree,

Take distinct count of clients only if all the 3 rows satisfies the condition. From the Above data for client A we have all the 3 rows satisfies the condition.So the count result is 1

Re: Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer

Tanushree_Kapse — Tue, 31 Aug 2021 10:39:18 GMT

Hi MITHUN_NAIR ,

Mark this as a solution if I answered your question. Kudos are always appreciated.

Thanks.