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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
praveenpasila
Advocate IV
Advocate IV

Issue with Top N

Hi All,

I am trying to achieve top N scenario Below is the data model

praveenpasila_0-1691934802910.png

 

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

IdType
1AAcc
2ANon Acc
3AAcc
4ANon Acc

 

Table H

Idclild IdSalesDate
1A11520230630
1A121020230630
1A13520230630
2A212020230630
2A23520230630
3A351520230630
3A552020230630
3A653020230630
4A452020230630
1A11520230731
1A121020230731
1A13520230731
2A212020230731
2A23520230731
3A351520230731
4A452020230731
3A552020230731
3A653020230731

 

Table S

clild Id
11
12
13
21
23
35
45
55
65

 

Table D

 

clild IdMetricValue
11CL11.5
12CL11.8
13CL12.1
21CL12.4
23CL12.7
35CL13
45CL13.3
11CL21.1
12CL21.5
13CL21.9
21CL22.3
23CL22.7
35CL23.1
45CL23.5
11CL31.2
12CL32
13CL35.2
21CL38.4
23CL311.6
35CL314.8
45CL318
55CL310
65CL316

 

Like image below nothing is being displayed when I am showing top N clild id's from Table S

praveenpasila_1-1691935338828.png

 

Thank you all in advance

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @praveenpasila ,

 

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.

vrzhoumsft_0-1692088486873.png

 

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.

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:

 

Idchild IdSalesDate
1A11520230630
1A121020230630
1A13520230630
2A212020230630
2A23520230630
3A351520230630
3A552020230630
3A653020230630
4A452020230630
1A11520230731
1A121020230731
1A13520230731
2A212020230731
2A23520230731
3A351520230731
4A452020230731
3A552020230731
3A653020230731
1A351520230731
2A111520230731
1A65520230731
5A55520230731

 

just to make sure added new account as well in Table E:

IdType
1AAcc
2ANon Acc
3AAcc
4ANon Acc
5AAcc

 

Please see the results below:

 

praveenpasila_0-1693581068578.png

 

Can you please suggest.

 

Thanks in advance

 

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.