Forum Discussion

latifshaikh145's avatar
latifshaikh145
Regular Visitor
1 year ago
Solved

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?


Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

     

     

    Please see attached sample pbix

    Note: Ignore the other pages.

8 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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()
    )

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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?

     

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        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.

         

         

        Please see attached sample pbix

        Note: Ignore the other pages.

  • latifshaikh145's avatar
    latifshaikh145
    Regular Visitor
    1 year ago

    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".

    • rajendraongole1's avatar
      rajendraongole1
      Icon for Super User rankSuper User
      1 year ago

      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.