Forum Discussion
Values not in another table based on topN
- 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 _rankThe pbix is attached
https://1drv.ms/u/s!AkrysYUHaNRvhcVzAbrcNKeeaU62Dw?e=cmTuJT
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