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
MITHUN_NAIR
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 =
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
Tanushree_Kapse
Impactful Individual
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.

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

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

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

Hi @MITHUN_NAIR ,

 

Tanushree_Kapse_0-1630406306443.png

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

 

Thanks.

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.