Forum Discussion

FarsH's avatar
FarsH
New Member
3 years ago

How to Identify top Performing based on selected parameter

hi, I'm working on a report and I want to give flexibilty to the user such that each time the
user select a certain product from the slicer "Products", and chooses a desired parameter from the "Metrics Value",
he's going to have cards of top/bottom (country, buyertype, month and day) based on his selection.

I've tried effort and found that parameters can't be included in DAX measures, hence,
I've resolved to using if statement below to control my selection,

 

TopT = IF(
    SELECTEDVALUE('Values'[Product SlicerP Order]) = 0,
    RANKX(ALLSELECTED('Mediterranean Cameras Company'[Product]), [Net Sales],,DESC) <= 1,

    IF(
    SELECTEDVALUE('Values'[Product SlicerP Order]) = 1,
    RANKX(ALLSELECTED('Mediterranean Cameras Company'[Product]), [Net Profit],,DESC) <= 1,

    IF(
    SELECTEDVALUE('Values'[Product SlicerP Fields]) = 2,
    RANKX(ALLSELECTED('Mediterranean Cameras Company'[Product]), [Net Profit Margin],,DESC) <= 1,

    IF(
    SELECTEDVALUE('Values'[Product SlicerP Fields]) = 3,
    RANKX(ALLSELECTED('Mediterranean Cameras Company'[Product]), [Total Unit Sold],,DESC) <= 1))))

 


but its yielded no result.

 

amitchandak Sahir_Maharaj 

My Screen

Interesting Links
Need Help
Tips and Tricks

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello FarsH,

     

    To generate the desired ranking, you can modify your approach and use the SWITCH function instead of IF statements to handle multiple conditions.

     

    Could you please try this:

     

    TopT =
VAR SelectedProduct = SELECTEDVALUE('Values'[Product SlicerP Order])
VAR SelectedMetric = SELECTEDVALUE('Values'[Metrics Value])
RETURN
SWITCH(
    SelectedMetric,
    "Net Sales", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Net Sales],,DESC) <= 1,
    "Net Profit", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Net Profit],,DESC) <= 1,
    "Net Profit Margin", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Net Profit Margin],,DESC) <= 1,
    "Total Unit Sold", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Total Unit Sold],,DESC) <= 1,
    BLANK()
)

     

    The SWITCH function checks the selected metric value and uses the appropriate RANKX function to generate the ranking of top/bottom values based on the selected product.

     

    The RANKX function filters the table based on the selected product and then calculates the ranking of the values based on the selected metric.

     

    Let me know if you might require further assistance.