Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
regnidem
Frequent Visitor

Count of clients who meet a measure requirement.

Hello all,

 

This is probably a simple question, but one that I can't seem to accomplish because I'm terrible with DAX.

 

I have a table that lists Clients, Service Providers, Dates of Service, and Status for that visit.

 

Spoiler
ClientServerDateStatus
1111John Doe1/1/2018No Show
1111John Doe1/2/2018Scheduled
1111John Doe1/3/2018Scheduled
1234John Doe1/1/2018Scheduled
1234John Doe1/2/2018Scheduled
9999Jane Smith1/1/2018Scheduled
9999Jane Smith1/2/2018Scheduled
9999Jane Smith1/3/2018Scheduled
9999Jane Smith1/4/2018Scheduled
8765Jane Smith1/5/2018Scheduled
8765Jane Smith1/6/2018Scheduled
8765Jane Smith1/7/2018Scheduled

I have a measure that gets me the No Show rate per client so I can have a matrix visualization similar to this:

 

ServerClientNo Show Rate
John Doe  
 111150%
 12340%
Jane Smith 
 99990%
 87650%

 

 

Now, I'm trying to come up wit a way to show, in a different visualization, the number of clients a Server has with a greater than 25% No show rate.  I've tried the following, but it doesn't work:

 

Measure = COUNTX(filter(Services, Services[No Show Rate] >= 0.28), DISTINCTCOUNT(Services[Client]))

 

How can I write this measure to get what I'm looking for?  It should be returning the following:

 

ServerNumber of Clients >=25% No Show Rate
John Doe1
Jane Smith0
1 ACCEPTED SOLUTION

Hey,

 

my measure to calculate the [No show rate] looks like this:

 

No Show Rate = 
var clientNoShows =
CALCULATE(
    COUNT('Table1'[Date])
    ,'Table1'[Status] = "No Show"
)
var clientVisits =
CALCULATE(
    COUNT('Table1'[Date])
    ,ALL('Table1'[Status])
)
return
DIVIDE(clientNoShows,clientVisits,0)

 

The measure to "count" the clients per server whose [no show rate] exceeds a given threshold looks like this

 

Measure = 
SUMX(
    VALUES('Table1'[Server])
    ,CALCULATE(
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    VALUES(Table1[Client])
                    ,"theNoShowRate",[No Show Rate]
                )
                ,[theNoShowRate] > 0.25
            )
        )
    )
    )

This allows to create a report like this, please be aware that I added a client 815 to server John Doe: one visit with status No Show

 

image.png

 

Hopefully this is what you are looking for

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @regnidem,

 

Please try this formula below which may meet your requirement.

 

Number of Clients = 
VAR a =
    CALCULATE (
        COUNT ( Table1[lient] ),
        FILTER ( 'Table1', [No Show Rate] > 0.25 )
    )
RETURN
    IF ( ISBLANK ( a ), 0, a )

Here is the output.

 

Capture.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomMartens
Super User
Super User

Hey,

 

I'm wondering how you calculate the "No show rate", from the sample data you provided I would assume that this rate would be 1/3 for client 1111 / server John Doe?

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey,

 

my measure to calculate the [No show rate] looks like this:

 

No Show Rate = 
var clientNoShows =
CALCULATE(
    COUNT('Table1'[Date])
    ,'Table1'[Status] = "No Show"
)
var clientVisits =
CALCULATE(
    COUNT('Table1'[Date])
    ,ALL('Table1'[Status])
)
return
DIVIDE(clientNoShows,clientVisits,0)

 

The measure to "count" the clients per server whose [no show rate] exceeds a given threshold looks like this

 

Measure = 
SUMX(
    VALUES('Table1'[Server])
    ,CALCULATE(
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    VALUES(Table1[Client])
                    ,"theNoShowRate",[No Show Rate]
                )
                ,[theNoShowRate] > 0.25
            )
        )
    )
    )

This allows to create a report like this, please be aware that I added a client 815 to server John Doe: one visit with status No Show

 

image.png

 

Hopefully this is what you are looking for

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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