Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Brighton10
Helper II
Helper II

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. 

 

Table1Table1Table2Table2Top3Top3Top5Top5

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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

smpa01_1-1633035249060.png

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

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

@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

smpa01_1-1633035249060.png

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

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
amitchandak
Super User
Super User

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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!