Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This seems like a tricky measure to create and I have no idea where to start. I have a table with Client and Visit Date. Clients can come in multiple times per month. I'm trying to figure out
1) Which clients had at least one visit per month.
2) Count of those clients
3) Total number of visits by those clients.
Attached is a small example of what I'm trying to accomplish.
Thanks in advance for the help.
Solved! Go to Solution.
Hi @mregnide ,
1.Create a calculated column to display the Month. And create another calculated column to display clients who had at least one visit per month.
Month = MONTH([Visit Date])
Clients who had at least one visit per month =
IF (
CALCULATE (
DISTINCTCOUNT ( 'Table'[Month] ),
ALLEXCEPT ( 'Table', 'Table'[Client] )
)
= CALCULATE ( DISTINCTCOUNT ( 'Table'[Month] ), ALL ( 'Table' ) ),
1
)
2.Put the Clients who had at least one visit per month column into the Filters. Set show items when the value is 1.
3.Create a measure to count the number of cilents who had at least one visit per month.
Count of clients who had at least one visit per month =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Client] ),
FILTER ( 'Table', [Clients who had at least one visit per month] = 1 )
)
4.Create a measure to count the total number of visits by those clients.
Total number of visits by those clients = COUNT('Table'[Clients who had at least one visit per month])
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mregnide ,
1.Create a calculated column to display the Month. And create another calculated column to display clients who had at least one visit per month.
Month = MONTH([Visit Date])
Clients who had at least one visit per month =
IF (
CALCULATE (
DISTINCTCOUNT ( 'Table'[Month] ),
ALLEXCEPT ( 'Table', 'Table'[Client] )
)
= CALCULATE ( DISTINCTCOUNT ( 'Table'[Month] ), ALL ( 'Table' ) ),
1
)
2.Put the Clients who had at least one visit per month column into the Filters. Set show items when the value is 1.
3.Create a measure to count the number of cilents who had at least one visit per month.
Count of clients who had at least one visit per month =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Client] ),
FILTER ( 'Table', [Clients who had at least one visit per month] = 1 )
)
4.Create a measure to count the total number of visits by those clients.
Total number of visits by those clients = COUNT('Table'[Clients who had at least one visit per month])
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-stephen-msft , that was very helpful!
One quick question: is it possible by changing the calculated column or writing a measure so that if I change the date range via slicer that the count and which clients meet the critera changes based on that date range?
Hi @mregnide ,
It is achievable, as long as you use the visit date as a slicer, there is no need to change the calculated column or the measure.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be a Super User!
I could not see an option to upload a csv, but here is the table.
Client | Visit Date |
A | 1/1/2020 |
B | 1/5/2020 |
C | 1/15/2020 |
D | 1/20/2020 |
A | 2/1/2020 |
B | 2/15/2020 |
A | 3/1/2020 |
B | 3/5/2020 |
C | 3/15/2020 |
A | 4/1/2020 |
B | 4/5/2020 |
D | 4/20/2020 |
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |