Values not in another table based on topN

[Brighton10]

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

[smpa01 · Accepted solution]

@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

[amitchandak]

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