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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.