Forum Discussion

Brighton10's avatar
Brighton10
Helper II
4 years ago
Solved

Values not in another table based on topN

Greetings PowerBI community   I have 2 tables, Table1 and Table2. I have created 2 table visuals showing the top3 and top5 C_names based on the ID count. Now i need to compare these topN visuals an...
  • smpa01's avatar
    4 years ago

    Brighton10  if you have two source table as following

     

    |   t1         |
    |----|---------|
    | ID | C_name  |
    |----|---------|
    | a1 | tim     |
    | a1 | sarah   |
    | a2 | sarah   |
    | a2 | Alaine  |
    | a2 | max     |
    | a3 | alex    |
    | a4 | mike    |
    | a5 | mike    |
    | a5 | michael |
    | a5 | Timmy   |
    | a5 | Alaine  |
    | a6 | Alaine  |
    
    |    t2            |
    |-----|------------|
    | ID  | C_name     |
    |-----|------------|
    | a1  | Timmy      |
    | a1  | sarah      |
    | a2  | Timmy      |
    | a2  | Alaine     |
    | a2  | max        |
    | a2  | max        |
    | a3  | Timmy      |
    | a4  | mike       |
    | a5  | mike       |
    | a5  | Alaine     |
    | a5  | Timmy      |
    | a5  | Alaine     |
    | a6  | Alaine     |
    | a7  | unrelated1 |
    | a7  | unrelated1 |
    | a8  | unrelated2 |
    | a8  | unrelated2 |
    | a8  | unrelated2 |
    | a8  | unrelated2 |
    | a9  | unrelated3 |
    | a9  | unrelated3 |
    | a9  | unrelated3 |
    | a9  | unrelated3 |
    | a9  | unrelated3 |
    | a9  | unrelated3 |
    | a10 | unrelated4 |
    | a10 | unrelated4 |
    | a10 | unrelated4 |
    | a10 | unrelated4 |
    | a10 | unrelated4 |
    | a10 | unrelated4 |
    | a10 | unrelated4 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a11 | unrelated5 |
    | a12 | unrelated0 |

     

    and you want explicit measure to return the top5 count of t2[id] that are not in top3 count of t1[id]

    They can be achieved as following

     

    Count = 
    CALCULATE (
        COUNT ( t2[ID] ),
        CALCULATETABLE (
            t2,
            EXCEPT (
                VALUES ( t2[C_name] ),
                SUMMARIZE (
                    TOPN (
                        3,
                        GROUPBY ( t1, t1[C_name], "@X", COUNTX ( CURRENTGROUP (), t1[ID] ) ),
                        [@X]
                    ),
                    t1[C_name]
                )
            )
        )
    )
    
    t2CountRanking = RANKX(ALL(t2[C_name]),[Count],,DESC,Dense)

     

     and then by applying following visual level filter

    This measure actually replicates the following DAX query

     

    Table =
    VAR _base =
        CALCULATETABLE (
            t2,
            EXCEPT (
                VALUES ( t2[C_name] ),
                SUMMARIZE (
                    TOPN (
                        3,
                        GROUPBY ( t1, t1[C_name], "@X", COUNTX ( CURRENTGROUP (), t1[ID] ) ),
                        [@X]
                    ),
                    t1[C_name]
                )
            )
        )
    VAR _count =
        ADDCOLUMNS (
            _base,
            "Count", CALCULATE ( COUNT ( t2[ID] ), ALLEXCEPT ( t2, t2[C_name] ) )
        )
    VAR _rank =
        FILTER (
            ADDCOLUMNS ( _count, "rank", RANKX ( _count, [Count],, DESC, DENSE ) ),
            [rank] <= 5
        )
    RETURN
        _rank

     

    The pbix is attached

    https://1drv.ms/u/s!AkrysYUHaNRvhcVzAbrcNKeeaU62Dw?e=cmTuJT