Forum Discussion
Issue with Top N
Hi All,
I am trying to achieve top N scenario Below is the data model
I wanted to use same column (ID) as filter twice to achieve this I am using below measure by creating two disconnected tables
The above measure is working as expected except for one view where I need top N child id's
can some one please guide me on how to make this work
Below is sample data of 4 table I am connected to
Table E
| Id | Type |
| 1A | Acc |
| 2A | Non Acc |
| 3A | Acc |
| 4A | Non Acc |
Table H
| Id | clild Id | Sales | Date |
| 1A | 11 | 5 | 20230630 |
| 1A | 12 | 10 | 20230630 |
| 1A | 13 | 5 | 20230630 |
| 2A | 21 | 20 | 20230630 |
| 2A | 23 | 5 | 20230630 |
| 3A | 35 | 15 | 20230630 |
| 3A | 55 | 20 | 20230630 |
| 3A | 65 | 30 | 20230630 |
| 4A | 45 | 20 | 20230630 |
| 1A | 11 | 5 | 20230731 |
| 1A | 12 | 10 | 20230731 |
| 1A | 13 | 5 | 20230731 |
| 2A | 21 | 20 | 20230731 |
| 2A | 23 | 5 | 20230731 |
| 3A | 35 | 15 | 20230731 |
| 4A | 45 | 20 | 20230731 |
| 3A | 55 | 20 | 20230731 |
| 3A | 65 | 30 | 20230731 |
Table S
| clild Id |
| 11 |
| 12 |
| 13 |
| 21 |
| 23 |
| 35 |
| 45 |
| 55 |
| 65 |
Table D
| clild Id | Metric | Value |
| 11 | CL1 | 1.5 |
| 12 | CL1 | 1.8 |
| 13 | CL1 | 2.1 |
| 21 | CL1 | 2.4 |
| 23 | CL1 | 2.7 |
| 35 | CL1 | 3 |
| 45 | CL1 | 3.3 |
| 11 | CL2 | 1.1 |
| 12 | CL2 | 1.5 |
| 13 | CL2 | 1.9 |
| 21 | CL2 | 2.3 |
| 23 | CL2 | 2.7 |
| 35 | CL2 | 3.1 |
| 45 | CL2 | 3.5 |
| 11 | CL3 | 1.2 |
| 12 | CL3 | 2 |
| 13 | CL3 | 5.2 |
| 21 | CL3 | 8.4 |
| 23 | CL3 | 11.6 |
| 35 | CL3 | 14.8 |
| 45 | CL3 | 18 |
| 55 | CL3 | 10 |
| 65 | CL3 | 16 |
Like image below nothing is being displayed when I am showing top N clild id's from Table S
Thank you all in advance
3 Replies
Hi Anonymous ,
I suggest you to create a measure for average value.
Firstly update [OR Filter] measure.
OR Filter = IF ( OR ( SELECTEDVALUE ( 'Table H'[Id] ) IN ALLSELECTED ( Acc[Id] ), // Checks if current Account is in slicer selection SELECTEDVALUE ( 'Table H'[Id] ) IN ALLSELECTED ( 'Non Acc'[Id] ) // Checks if current Non Account is in slicer selection ), 1, // Return 1 if value is selected in one or both of the slicers 0 // Else return 0 )Measure:
Measure = VAR _Summairze = SUMMARIZE ( FILTER ( 'Table S', [OR Filter] = 1 ), 'Table S'[clild Id], "ID", CALCULATE ( MAX ( 'Table H'[Id] ) ), "Average of Value", CALCULATE ( AVERAGE ( 'Table D'[Value] ) ) ) RETURN SUMX ( _Summairze, [Average of Value] )Add it into TopN filter in Child ID column. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Anonymous this is working fine here dont know in my real time scenario created same two measures but its not working still showing blank view
I am using column "Id" from Acc and Non Acc Tables in home page as slicers
if I use "Id" from Table E it showing fine but this can not be used as slicer as it will not show Non account data
I am not able to figure out if I am missing something data model set up is also same as you did.
Any thoughts please let me know.
Thanks for your support
Hi Anonymous ,
I have identified the problem. Tried multiple forum but was unable to solve the problem.
The problem is the same clild id's are being associated with multiple Id's
Please find the updated table below for Table H:
Id child Id Sales Date 1A 11 5 20230630 1A 12 10 20230630 1A 13 5 20230630 2A 21 20 20230630 2A 23 5 20230630 3A 35 15 20230630 3A 55 20 20230630 3A 65 30 20230630 4A 45 20 20230630 1A 11 5 20230731 1A 12 10 20230731 1A 13 5 20230731 2A 21 20 20230731 2A 23 5 20230731 3A 35 15 20230731 4A 45 20 20230731 3A 55 20 20230731 3A 65 30 20230731 1A 35 15 20230731 2A 11 15 20230731 1A 65 5 20230731 5A 55 5 20230731 just to make sure added new account as well in Table E:
Id Type 1A Acc 2A Non Acc 3A Acc 4A Non Acc 5A Acc Please see the results below:
Can you please suggest.
Thanks in advance