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

Regular Visitor

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

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.

5 REPLIES 5
Impactful Individual

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.

Regular Visitor

Hi Tanusree,

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

Impactful Individual

Hi @MITHUN_NAIR ,

VAR Selected_Date =SELECTEDVALUE ( Date[Date] )

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

Regular Visitor

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

Impactful Individual

Hi @MITHUN_NAIR ,

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

Thanks.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### 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