Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Client | Server | Date | Status |
1111 | John Doe | 1/1/2018 | No Show |
1111 | John Doe | 1/2/2018 | Scheduled |
1111 | John Doe | 1/3/2018 | Scheduled |
1234 | John Doe | 1/1/2018 | Scheduled |
1234 | John Doe | 1/2/2018 | Scheduled |
9999 | Jane Smith | 1/1/2018 | Scheduled |
9999 | Jane Smith | 1/2/2018 | Scheduled |
9999 | Jane Smith | 1/3/2018 | Scheduled |
9999 | Jane Smith | 1/4/2018 | Scheduled |
8765 | Jane Smith | 1/5/2018 | Scheduled |
8765 | Jane Smith | 1/6/2018 | Scheduled |
8765 | Jane Smith | 1/7/2018 | Scheduled |
I have a measure that gets me the No Show rate per client so I can have a matrix visualization similar to this:
Server | Client | No Show Rate |
John Doe | ||
1111 | 50% | |
1234 | 0% | |
Jane Smith | ||
9999 | 0% | |
8765 | 0% |
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:
Server | Number of Clients >=25% No Show Rate |
John Doe | 1 |
Jane Smith | 0 |
Solved! Go to 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
Hopefully this is what you are looking for
Regards,
Tom
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.
Best Regards,
Cherry
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
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
Hopefully this is what you are looking for
Regards,
Tom
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |