Filtering a grouped by table by slicer does not work

[Anonymous]

Hi,

I have a dataset like 

(table_x)

id	player_id	year
1	34	2020
2	35	2020
3	35	2021
4	35	2021
5	34	2021
6	21	2021

I have grouped this table via DAX (with GROUPBY(...))  to:

(table_y)

player_id	COUNT(id)
21	1
34	2
35	3

After that I linked that table_x.player_id to table_y.player_id (1:n relationship) and built a slicer for the year.

If I now want to slice the data to year 2021 I thought I would end up like:

player_id	COUNT(id)
21	1
34	1
35	2

but the slicer has no effect. Why? 

[Ashish_Mathur]

Hi,

You do not need to create a table_y.  From table_x, create a Table visual and drag player_id to the row labels.  Create a slicer from the Year column and select 2021.  Write this measure

Measure = countrows(Data)

Hope this helps.

[v-chenwuz-msft]

Hi @Anonymous,

It strange that is it a data table or a visual table?

If data table in power bi, it won't work with slicer. The data table will be saved when you created. 

If a visual table, it is unnecessary to create a table_y, table_x is enough to reach that result.

first , drag 'table_x'[player_id] to table visual (don't summarize) and 'table_x'[id] (count). Then create a slicer with 'table_x'[year].

If create table_y is necessary and drag 'table_y'[player_id] will works with the relationship 1:mangy between them.

If I misunderstood you, please let me know.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the answer. Sadly, i missed the last important point in my process. I do not only want to filter that table_y, I also want to group this table_y in a visual table like (for year: 2021): 

COUNT(id)	COUNT(COUNT(id))
1	2
2	1

How many times does a player occur once, twice, ... in that year. 

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous 

It some difficulty to do that, you can try this measure:

count(count(id)) =
VAR _1 =
    VALUES( Table_x[player_id] )
VAR _table_y =
    ADDCOLUMNS(
        _1,
        "count_id", COUNTROWS( FILTER( 'Table_x', [player_id] = EARLIER( Table_x[player_id] ) ) )
    )
VAR _group_table_y =
    SUMMARIZE(
        _table_y,
        [count_id],
        "count(count(id))", COUNTROWS( FILTER( _table_y, [count_id] = EARLIER( [count_id] ) ) )
    )
RETURN
    MAXX(
        FILTER( _group_table_y, [count_id] = SELECTEDVALUE( 'New Table'[Value] ) ),
        [count(count(id))]
    )

Before this measure, you need create new table in advance:

New Table = GENERATESERIES(1,COUNTROWS('Table_x'),1)

and the result :

I put my pbix file in the attachment you can refer

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.