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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unwanted Aggregation with dynamic Ranking

Hi, 

 

I am trying to create a dynamic ranking measure. Lets say I have n rows after every filter operation. Now I want the rows enumerated from 1.. n, according to their original id (which is unique). 

link to pbix  <- here is a reproduction of the error. Password: Example%5

I tried the solution by @Eirc_Zhang from this post but it does not work for me. Here is the code how I modiefied it, to use the index as the parameter for ranking. 

 

 

 

 

RankID = 
MINX (
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( ParentData );
            "index"; ParentData[Index];
            "rank"; RANKX ( ALLSELECTED ( ParentData ); ParentData[Index];; ASC; DENSE )
        );
        [index] = MAX ( ParentData[Index] )
    );
    [rank]
)

 

 

 

 

Everytime I use a visual with non unique field combinations, the rows are aggregated, as you can see in Visual (1) in my posted image. In Visual (2) the unique field "Index" is added and then there is no aggregation. The final goal would be to use a non unique field and the RankID togehter in a scatter plot (Visual 3). Since you can't add there another field there would be always an unwanted aggregation. Can someone help me to fix the problem? (I already managed to do it with a python visual, but I would rather have an interactive visual. I kinda wish, I could use python to calculate measures )

 

The visualsThe visuals

 

I actually don't fully understand the linked solution and would appreciate, if someone could help me to understand it a little better. For example I have a hard time to understand how the MINX function works, since the inner filter call just returns a table with a single row as output, as you can see in the next image. 

 

First argument for MINXFirst argument for MINX



Best regards, 
Andreas

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Let's say you have a physical table with five columns.  In Power BI you add one column to a visual.  Power BI will do a GROUPBY on that column.  You add one more column. Power BI will group by the two columns etc.

You continue to add columns to the visual, until all columns have been added. Power BI will still do a GROUPBY !!! 

To add insult to injury, Power BI is not case sensitive.  

 

Have fun.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

Let's say you have a physical table with five columns.  In Power BI you add one column to a visual.  Power BI will do a GROUPBY on that column.  You add one more column. Power BI will group by the two columns etc.

You continue to add columns to the visual, until all columns have been added. Power BI will still do a GROUPBY !!! 

To add insult to injury, Power BI is not case sensitive.  

 

Have fun.

Anonymous
Not applicable

Thanks for the explanation!

As a workaround I calculated NewFeature with NewFeature = Feature + 10^(-9) * (Index - MIN(All Indexes) . Therefore NewFeature still contains the information of Feature but is also unique due to the fact that index is unique. It is not a nice solution but at least it works as long the difference between the biggsest indext and the smallest index does not become too large. 

 

lbendlin
Super User
Super User

If you don't want Power BI to aggregate, don't feed it groupings. It's what it does. One could say it is one of the main design principles of Power BI. Good luck winning that fight.

Anonymous
Not applicable

Hi Ibendlin, 


thanks for your reply! 

Could you explain what you mean bei groupings and where I used one?  Do you mean the rankx Call? 

I just don't get how the level of Aggregation changes with the fields I use in a visual. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors