Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering a grouped by table by slicer does not work

Hi,

 

I have a dataset like 

 

(table_x)

idplayer_idyear
1342020
2352020
3352021
4352021
5342021
6212021

 

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

 

(table_y)

player_idCOUNT(id)
211
342
353

 

 

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_idCOUNT(id)
211
341
352

 

but the slicer has no effect. Why? 

Need Help
Tips and Tricks
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

4 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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))
      12
      21

       

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

       

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.