Skip to main content
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

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.


Sample Data.PNG


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 =
VAR CountofClients =
DISTINCTCOUNT ( Clients[Client] ),
FILTER ( Clients, Clients[KPI] = "Not Met" || Clients[Quality] = "Not Met" ),
Clients[Date] = Selected_Date




Impactful Individual
Impactful Individual



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.



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



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" ))

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




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



Helpful resources

Europe Fabric Conference

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.

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.

Top Solution Authors
Top Kudoed Authors