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.
Hi experts,
Having this table (this is a portion of a huge table):
Id | Sport | event |
User1 | Sport1 | Event1 |
User2 | Sport1 | Event2 |
User3 | Sport2 | Event3 |
User3 | Sport2 | Event4 |
User3 | Sport2 | Event5 |
USer3 | Sport2 | Event6 |
User4 | Sport3 | Event7 |
User4 | Sport3 | Event8 |
User5 | Sport2 | Event9 |
User6 | Sport4 | Event10 |
User6 | Sport4 | Event11 |
User6 | Sport4 | Event12 |
User6 | Sport4 | Event13 |
User6 | Sport4 | Event14 |
User6 | Sport4 | Event15 |
User3 | Sport1 | Event1 |
User3 | Sport1 | Event5 |
I want to create a table like this:
Sport1 | ||
QtEvents | Users | |
1 | 2 | |
2 | 1 | |
3 | 0 |
Sport would be the filter, and I want to get the number of users who belongs to an event. In this case, User3, belongs to 2 events [Event1, Event5]; User1 belongs to 1 event [Event1] and User2, belongs to one event [Event2].
I have an idea about what I want to do, but can't translate it to DAX.
1. Count of Id.
2. Count of rows, form the previous step.
with this steps, I can get something like
From this, I want to apply step 2, in this way, I think I could create the result table with. This can be read to something like, user1 and user2, belong to 1 Event; User3, belongs to 2 events.
Please see the atached file here!
Any suggestion?
Solved! Go to Solution.
Hi @Anonymous
count of events for each user = CALCULATE ( DISTINCTCOUNT ( Eventos[event] ), FILTER ( ALLEXCEPT ( Eventos, Eventos[Sport] ), Eventos[Id] = EARLIER ( Eventos[Id] ) ) )Create a measure
count of Users = CALCULATE ( DISTINCTCOUNT ( Eventos[Id] ), FILTER ( ALLSELECTED ( Eventos ), Eventos[count of events for each user] = MAX ( Eventos[count of events for each user] ) ) )
Check this file
The final table you want is not very clear. Add a few tables.
Thanks for your response @amitchandak .
When I select Sport1, the output table should be:
QtEvents | QtUsers |
1 | 2 |
2 | 1 |
3 | 0 |
4 | 0 |
5 | 0 |
6 | 0 |
Sport2:
QtEvents | QtUsers |
1 | 1 |
2 | 0 |
3 | 0 |
4 | 1 |
5 | 0 |
6 | 0 |
Sport3:
QtEvents | QtUsers |
1 | 0 |
2 | 1 |
3 | 0 |
4 | 0 |
5 | 0 |
6 | 0 |
Sport4:
QtEvents | QtUsers |
1 | 0 |
2 | 0 |
3 | 0 |
4 | 0 |
5 | 0 |
6 | 1 |
Thanks
Hi @Anonymous
count of events for each user = CALCULATE ( DISTINCTCOUNT ( Eventos[event] ), FILTER ( ALLEXCEPT ( Eventos, Eventos[Sport] ), Eventos[Id] = EARLIER ( Eventos[Id] ) ) )Create a measure
count of Users = CALCULATE ( DISTINCTCOUNT ( Eventos[Id] ), FILTER ( ALLSELECTED ( Eventos ), Eventos[count of events for each user] = MAX ( Eventos[count of events for each user] ) ) )
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 |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |