Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to process multiple selections from a slicer?

Hi,
 
I simplified the example here.
Had a slicer where you could choose one value of 'Max' and 'Min'. Based on the choice the bar gets a color.
 
Measure = 
VAR _metric = SELECTEDVALUE('Chosen Values'[ID])
VAR _color =
SWITCH(
    TRUE,
    _metric = 1 && [Sum Quantity] = _maxQTY, "Green",
    _metric = 2 && [Sum Quantity] = _minQTY, "Blue",
    "Light Grey"
)
RETURN _color
 
But now I want to be able to color the Max as well as Min. So instead of SELECTEDVALUE i need something else.
And the SWITCH probably needs to be two times IF?

Who can help how to code this?

regards Ron



 
 
  • Hi Anonymous 

    If i understood you correctly you are trying to catch 2 values from the slicer , you can use min for lowest selection,
    max for highest. 

    For example, with the simple list, it looks like :

     


    your formula will look something like :

    Measure = 
    VAR  metric_min = min('Chosen Values'[ID])
    Var metric_max = max(Chosen Values'[ID])
    .
    .
    .
     
    if i missed something 

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

  • MFelix's avatar
    MFelix
    2 years ago

    Hi Anonymous ,

     

    Try the following code:

    YOY difference CF = 
    
    VAR _aantalmaanden = 
    COUNTROWS(
        ALL('Date'[Month Name Short], 'Date'[Month])
    )
    
    VAR _metric = SELECTEDVALUE('Top 3'[Num])
    
    VAR colorall= 
    SWITCH(
        TRUE,
         [RANK YOY difference] <= 3 && [YOY difference %] > 0 , "Green",                      -- Top 3 Increases
         [RANK YOY difference] > _aantalmaanden - 3  && [YOY difference %] < 0, "Blue",      -- Top 3 Decreases
         "Light Grey"
    )
    
    VAR _color =
    SWITCH(
        TRUE,
         _metric = 1 && 
         [RANK YOY difference] <= 3 && [YOY difference %] > 0 , "Green",                      -- Top 3 Increases
         _metric = 2 && 
         [RANK YOY difference] > _aantalmaanden - 3  && [YOY difference %] < 0, "Blue",      -- Top 3 Decreases
         "Light Grey"
    )
    
    RETURN
    IF(COUNTROWS('Top 3') = 1, _color, colorall)

     

     

10 Replies

  • Hi Anonymous 

    If i understood you correctly you are trying to catch 2 values from the slicer , you can use min for lowest selection,
    max for highest. 

    For example, with the simple list, it looks like :

     


    your formula will look something like :

    Measure = 
    VAR  metric_min = min('Chosen Values'[ID])
    Var metric_max = max(Chosen Values'[ID])
    .
    .
    .
     
    if i missed something 

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Ritaf1983 

      no, that's not what I mean. I will try to upload a pbix today to clarify the issue.

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User

      Hi Anonymous ,

       

      Try the following code:

      YOY difference CF = 
      
      VAR _aantalmaanden = 
      COUNTROWS(
          ALL('Date'[Month Name Short], 'Date'[Month])
      )
      
      VAR _metric = SELECTEDVALUE('Top 3'[Num])
      
      VAR colorall= 
      SWITCH(
          TRUE,
           [RANK YOY difference] <= 3 && [YOY difference %] > 0 , "Green",                      -- Top 3 Increases
           [RANK YOY difference] > _aantalmaanden - 3  && [YOY difference %] < 0, "Blue",      -- Top 3 Decreases
           "Light Grey"
      )
      
      VAR _color =
      SWITCH(
          TRUE,
           _metric = 1 && 
           [RANK YOY difference] <= 3 && [YOY difference %] > 0 , "Green",                      -- Top 3 Increases
           _metric = 2 && 
           [RANK YOY difference] > _aantalmaanden - 3  && [YOY difference %] < 0, "Blue",      -- Top 3 Decreases
           "Light Grey"
      )
      
      RETURN
      IF(COUNTROWS('Top 3') = 1, _color, colorall)

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi MFelix 

        exactly what I need. Thnx for helping

  • Anonymous You already have a great solution from MFelix  but out of curiosity, I have a question.  Why do you want to check  > 0 or < 0 values for increase and decrease? For example, you can have all positive increases and in this case bottom 3 will be with less increase %. 

    • Anonymous's avatar
      Anonymous
      Not applicable

      HI parry2k 

      good question.
      In my try-out visual I want to show top3 increase and decrease together.
      But it depends what you mean by increase/decrease.

      If increase top 3 are only posts that increased compared to previous year, so a positive raise, then you have to exclude the negative ones. So, assume 10 out of 12 months decreased, then your top 3 increase will be a top 2.
      Same counts for top 3 decrease. Exclude the positive ones.

  • Anonymous @I get that but my question is if there is no negative or if there is no positive. 

    if there is no negative then you will not get bottom 3 and  if there is no positive then you will never get top 3.

     

    Maybe this is whet you want, as I said I am just curious to know.