Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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