Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |