Help with increase of displayed data

[Chris_68]

Hi!

I’m using a ranking measure to limit the number of line items displayed in a table. However, when I add the "Minor" field to the table, it increases the number of displayed items beyond the specified TopN limit.

The link to the Power Bi file

https://www.dropbox.com/scl/fi/57f6xcnflx1hhez97okwy/Test.pbix?rlkey=23mfhh7pe0c4dq6tg7rnz845r&st=vb...

or

Test.pbix

Could you please advise how I can prevent this from happening?

Thank you.

[sanalytics · Accepted solution]

@Chris_68 
I am not sure regarding your exact requirement and desired output.Are you looking for top 10 qty including item and minor?? if yes. then you can try the below code

Rank = 
VAR _Rank =
RANKX(
 ALL( Parts[Item],Parts[Minor] ),[TotalQty],,DESC )
VAR _Result = 
IF(
 _Rank <= [TopN Value],1 )
 RETURN
 _Result

below screenshot

Regards

sanalytics

[v-karpurapud]

Hi @Chris_68 

We are happy to hear your issue has been resolved. Please mark the insights as 'Accept as solution' to help others with similar questions find it more easily.

Thank you.

[v-karpurapud]

Hi @Chris_68 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @DataNinja777 and @sanalytics  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? This will help other community members solve similar problems faster.

Thank you.

[Chris_68]

HI,

Yes, it has resolved the issue.

Thank you

[v-karpurapud]

Hi @Chris_68 

We are glad to hear that your issue is resolved. Please mark your it as 'Accept as solution' so others with similar issues can find it easily.


Thank you.

[DataNinja777 · Accepted solution]

Hi @Chris_68 ,

This is a common issue in DAX caused by how filter context works in Power BI. When you add the Minor column to your table, you change the context for each row from just an Item to a unique combination of Item and Minor. Your original ranking measure is then evaluated in this new, more detailed context, which causes more rows to appear than you intended.

To resolve this, you need to modify your Rank By Item measure to ignore the filter context coming from the Minor column. This is done by wrapping the [TotalQty] argument inside a CALCULATE function. This ensures the ranking is always based on the total quantity for the entire Item, regardless of what other details like Minor are present in the table. Your other measures can remain the same.

Your current formula is:

Rank By Item = RANKX ( ALL ( Parts[Item] ), [TotalQty],, DESC )

You should update it to the following corrected formula:

Rank By Item =
RANKX (
    ALL ( Parts[Item] ),
    CALCULATE ( [TotalQty], ALL ( Parts[Minor] ) ),
    ,
    DESC
)

After making this one change, your table visual will correctly filter to the Top N items first and then display their associated Minor codes, matching your intended result.

Best regards,

[Chris_68]

Thank you very much for your suggestion. I’ve realized that the data increase is due to different groups of minors being assigned the same item number.

In any case, I really appreciate your help. Thanks again!

[sanalytics · Accepted solution]

@Chris_68 
I am not sure regarding your exact requirement and desired output.Are you looking for top 10 qty including item and minor?? if yes. then you can try the below code

Rank = 
VAR _Rank =
RANKX(
 ALL( Parts[Item],Parts[Minor] ),[TotalQty],,DESC )
VAR _Result = 
IF(
 _Rank <= [TopN Value],1 )
 RETURN
 _Result

below screenshot

Regards

sanalytics