Create a column based on a measure

[Anonymous]

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?

[amitchandak]

Check this file

[amitchandak]

The final table you want is not very clear.  Add a few tables.

[Anonymous]

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

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Create a column

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] )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.