Slicer for certain values

[MurtoMan]

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 certain values in Value A and it should show percentage between 0 - 100 (not values itself)

What kind of DAX code should I use?

[Syndicate_Admin]

Hi Wenbin,

This looks reasonable, but can you explain what is the purpose of this table?

Are these 3 values enough?

[v-zhouwen-msft]

Hi @MurtoMan , @Syndicate_Admin ,

Regarding your question, this expression creates a table of consecutive values.(Starting at 0 and ending at 1.01, each increase is 0.01, i.e., 1 percent.)

GENERATESERIES function - DAX | Microsoft Learn

This allows you to select the corresponding value via the slicer.

[MurtoMan]

Hi, @v-zhouwen-msft 

Ok I see.

But what comes to Index there is a problem.

Values A or B are not unique. So there would be different index values for same values A or B.

[v-zhouwen-msft]

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]

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]

Hi @v-zhouwen-msft 

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?

[MurtoMan]

Big thanks @v-zhouwen-msft !

Now it works like a charm 😊

[MurtoMan]

One extra question.

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

[v-zhouwen-msft · Accepted solution]

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.

[v-zhouwen-msft]

Hi @MurtoMan ,

Regarding your question, this is my thought process.

The Table data is shown below:

Please follow these steps:

1.Add an index column to determine which values to modify with the slicer.

2.Use the following DAX expression to create a table

Table 2 = GENERATESERIES(0.00,1.01,0.01)

3.Use the following DAX expression to create a measure(Assuming that the index columns corresponding to the specific values to be modified are 1, 2, 3)

MEASURE =
VAR _a =
    MAX ( 'Table 2'[Value] )
VAR _b =
    IF (
        _a = CALCULATE ( MAX ( 'Table 2'[Value] ), ALL ( 'Table 2' ) ),
        SUM ( 'Table'[Value A] ) * SUM ( 'Table'[Value B] ),
        IF (
            SELECTEDVALUE ( 'Table'[Index] ) IN { 1, 2, 3 },
            _a
                * SUMX (
                    FILTER ( 'Table', 'Table'[Index] = SELECTEDVALUE ( 'Table'[Index] ) ),
                    'Table'[Value B]
                ),
            SUM ( 'Table'[Value A] ) * SUM ( 'Table'[Value B] )
        )
    )
RETURN
    _b

3.Final output

Displays the default value when the slicer has not made a selection.

After selecting the slicer

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.