Forum Discussion

MurtoMan's avatar
MurtoMan
Helper I
2 years ago
Solved

Slicer for certain values

I have a visualization that shows a comparison of certain values.  There is a simple calculation like Value A * Value B = Result (in bars) I want a slicer like this that changes only few cert...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MurtoMan ,

    The Table data is shown below:

    Please follow these steps:
    1. Use the following DAX expression to create a Table

     

    Table 2 = GENERATESERIES(1,100,1)

     

    2.Use the following DAX expression to create a measure

     

    Result = 
VAR _a = SELECTEDVALUE('Table'[Item])
VAR _b = SELECTEDVALUE('Table'[Factor])
VAR _c = SELECTEDVALUE('Table'[Value])
RETURN
IF( _a = "Shoes",
DIVIDE(_b,100) * MAX('Table 2'[Value]) * _c   , _b * _c
)

     

    3.Use the following DAX expression to create a measure

     

    _Factor = IF( SELECTEDVALUE('Table'[Item]) = "Shoes", DIVIDE(SELECTEDVALUE('Table'[Factor]),100) * MAX('Table 2'[Value]),SELECTEDVALUE('Table'[Factor]))

     

    4.Final output

     

     

     

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MurtoMan ,

What are your expected outcomes? Is it to change all the 0.35 in column 'Value A' to the selection in the slicer?

The expression I provided should be able to find the value in the corresponding column 'Value B' based on the index column

I modified the table data.

1.With no slicer selected(0.35 = 0.35 *1    0.7 = 0.35 *2   0.7 = 0.35 * 2 ........)

2.After selecting the slicer(0.96 = 0.96 * 1    1.92 = 0.96 *2   1.92 = 0.96 * 2 ......)

 

MurtoMan's avatar
MurtoMan
Helper I
2 years ago

Hi,

 

Yes the expected outcome is to change values in column 'Value A' with a slicer.

But indexing doesn't work in my case because max index is restricted to 1000. However, I have several million rows in my data.

  • MurtoMan's avatar
    MurtoMan
    Helper I
    2 years ago

    Hi Anonymous 

     

    Let's put it in this way.

     

    I have a table like this and I want to change the factor with a slicer in Power BI Desktop.

     

     

    It's easy to create a slicer like this for factor values

    But I want to turn the values in the slicer for shoes only from real values to percents (between 0 - 100).

    What is the way to do that?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi MurtoMan ,

      The Table data is shown below:

      Please follow these steps:
      1. Use the following DAX expression to create a Table

       

      Table 2 = GENERATESERIES(1,100,1)

       

      2.Use the following DAX expression to create a measure

       

      Result = 
VAR _a = SELECTEDVALUE('Table'[Item])
VAR _b = SELECTEDVALUE('Table'[Factor])
VAR _c = SELECTEDVALUE('Table'[Value])
RETURN
IF( _a = "Shoes",
DIVIDE(_b,100) * MAX('Table 2'[Value]) * _c   , _b * _c
)

       

      3.Use the following DAX expression to create a measure

       

      _Factor = IF( SELECTEDVALUE('Table'[Item]) = "Shoes", DIVIDE(SELECTEDVALUE('Table'[Factor]),100) * MAX('Table 2'[Value]),SELECTEDVALUE('Table'[Factor]))

       

      4.Final output

       

       

       

       

      Best Regards,
      Wenbin Zhou
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MurtoMan's avatar
      MurtoMan
      Helper I
      2 years ago

      Big thanks Anonymous !

      Now it works like a charm 😊

      • MurtoMan's avatar
        MurtoMan
        Helper I
        2 years ago

        One extra question.

        Is there a way to show total SUM of this Result column?