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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
latifshaikh145
New Member

Rank is not working in multiple dimension filter

I have created below paramater for multi dimensions ,

Dim Selector = {

    ("Supplier", NAMEOF('Transactions'[Suplier Name]), 0),
    ("Hotel", NAMEOF('Transactions'[Hotel Name]), 1),
    ("Agency", NAMEOF('Transactions'[Agency]), 2}

Separate rank calc for each dimensions, 
Example: Rank by Supplier = RANKX(ALL(Transactions[Suplier Name]), 'Measure Selector'[Selected Measure])

but my switch calc is not working here,

Rank =
    SWITCH(
        'Dim Selector'[Dimension Selector],
        "Supplier", [Rank by Supplier],
        "Hotel", [Rank by Hotel],
        "Agency", [Rank by Agency],
        BLANK() )

Separate rank calculation is working fine when entred individually but same is not working with Rank switch, can any one please make correction to get desire output?


1 ACCEPTED SOLUTION

And you seem to have created a calculated as 'Dim Selector'[Dimension Selector] isn't aggregated. Calculated columns, unfortunately, do no respond to slicer selections. They are not aware of what's been selected in a slicer.

 

Instead, create a measure that references the parameter order field otherwise you'll get an error similar to below:

"Parameter columns is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression".

 

Column switching rank = 
SWITCH (
    SELECTEDVALUE ( DimSelector[DimSelector Order] ),
    0, [Rank by Supplier],
    1, [Rank by Hotel],
    2, [Rank by Agency]   
)

Use the correct measure for each parameter order field value. Use the parameter field and  the measure  above in your visual.

danextian_0-1737730373852.pngdanextian_1-1737730382633.png

 

 

Please see attached sample pbix

Note: Ignore the other pages.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

8 REPLIES 8
latifshaikh145
New Member

hi @rajendraongole1 Thanks for the quick response while using this calculation below error is reflecting.

"Column [Dimension Selector] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression".

then in that case,  you can create a calculated column in the DIM Selector table to flatten it into a single unique key

UniqueKey = 'Dim Selector'[Dimension] & "-" & 'Dim Selector'[Expression]

 

check with UniqueKey column instead of Dimension Selector in your slicer and calculations.

 

Let me know if it works.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@rajendraongole1 What do you mean by "Dim Selector'[Expression]" here?

 

danextian
Super User
Super User

Hi @latifshaikh145 

What is  'Dim Selector'[Dimension Selector] in 'Dim Selector' table? Creating a field parameter from scratch creates three columns starting with the column name and followed by table name  + Fields and table name + Order. Is that a measurei in Dim Selector table or a column?

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@danextian Its a cloumn.

And you seem to have created a calculated as 'Dim Selector'[Dimension Selector] isn't aggregated. Calculated columns, unfortunately, do no respond to slicer selections. They are not aware of what's been selected in a slicer.

 

Instead, create a measure that references the parameter order field otherwise you'll get an error similar to below:

"Parameter columns is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression".

 

Column switching rank = 
SWITCH (
    SELECTEDVALUE ( DimSelector[DimSelector Order] ),
    0, [Rank by Supplier],
    1, [Rank by Hotel],
    2, [Rank by Agency]   
)

Use the correct measure for each parameter order field value. Use the parameter field and  the measure  above in your visual.

danextian_0-1737730373852.pngdanextian_1-1737730382633.png

 

 

Please see attached sample pbix

Note: Ignore the other pages.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@danextian Highly appreciated! this file helped me understand my mistake, actually I was adding as a colum for switch calc despite of measure. now I have changed it to Measure from Column and its working fine now.

rajendraongole1
Super User
Super User

Hi @latifshaikh145  - Your SWITCH statement appears to be correctly set up in principle, but the issue may be related to how the Dimension Selector value is being passed or recognized.

Use SELECTEDVALUE to ensure only one value is retrieved.

Selected Dimension = SELECTEDVALUE('Dim Selector'[Dimension Selector])

Now , use the measure:

 

Rank =
SWITCH(
[Selected Dimension],
"Supplier", [Rank by Supplier],
"Hotel", [Rank by Hotel],
"Agency", [Rank by Agency],
BLANK()
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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