Issue with Top N

[Anonymous]

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

OR Filter =

IF (

    OR (

        SELECTEDVALUE ( 'Table E'[Id] ) IN ALLSELECTED ( Acc[Id] ),

        // Checks if current Account is in slicer selection

        SELECTEDVALUE ( 'Table E'[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

)

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

[v-rzhou-msft]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-rzhou-msft ,

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

[Anonymous]

Thank you @v-rzhou-msft  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