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 and return only C_names present in table2. In this example this has to be timmy and max. 

 

Table1Table2Top3Top5

Need Help
  • smpa01's avatar
    smpa01
    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

     

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Brighton10 , Table1's ID count based on TOP 3 of table 2

     

    calculate(Count(Table1[ID]),filter(Table1, Table1[ID] in TOPN(3, allselected(Table2[ID]),calculate(Count(Table2[ID])),DESC)) )

     

     

  • smpa01's avatar
    smpa01
    Community Champion
    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