Switch and SelectedValue

[Bkurtsatar]

Hello, I'm experiencing an issue with a DAX code.

When I write the code as follows, I don't get any errors.

FilteredTableByRiskType =

VAR SelectedRisk = "A"

VAR FilteredTable =

FILTER(DB_Table,

        SWITCH(SelectedRisk ,

            "A", DB_Table[A]=1 ,

            "B", DB_Table[B] =1,

            "C", DB_Table[C]=1 ,

            "D", DB_Table[D] =1,

                        FALSE()

        )

)

RETURN

FilteredTable

However, if I write it like this, it returns an empty table. I'm also checking the SelectedRisk value in a table, and I'm able to retrieve it correctly.

FilteredTableByRiskType =

VAR SelectedRisk = SELECTEDVALUE('Risk_Table'[Risk])

VAR FilteredTable =

FILTER(DB_Table,

        SWITCH(SelectedRisk ,

            "A", DB_Table[A]=1 ,

            "B", DB_Table[B] =1,

            "C", DB_Table[C]=1 ,

            "D", DB_Table[D] =1,

                        FALSE()

        )

)

RETURN

FilteredTable

I also tried the following code for checking, and it returns "True."

IsTest = SELECTEDVALUE(Risk_Table[Risk])  = "A"

What can I do? Thank you.

[ValtteriN]

Hi,

You can't create table dynamically.

Here is an example:

Here we are trying to create a table which gains column values based on the selection.

The corresponding measure would be like this: 

Measure 6 = IF(SELECTEDVALUE('Calendar'[Date])=TODAY(),1,0)

Now on visual side this happens:

The table's value doesn't change but the measure does.

Despite this we can still use the table within variable e.g. like this:

Measure 7 = var Dynamicissue = ADDCOLUMNS({("A")},"Dynamic",IF(SELECTEDVALUE('Calendar'[Date])=TODAY(),1,0)) return

SUMX(Dynamicissue,[Dynamic])
Here the table is created within the measure's variable and thus gets the desired value.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Bkurtsatar]

Thank you for your response. Actually, what I want to do is as follows: Dynamically filter column names based on the value selected from a slider. I created a table to use in the slider:

Risk_Table = UNION(

ROW("Risk", "A"),

ROW("Risk", "B"),

ROW("Risk", "C"),

ROW("Risk", "D"),

ROW("Risk", "E"),

ROW("Risk", "F"),

ROW("Risk", "G") )

I added a slider based on the "Risk" column from this table. I want to search among the columns of table X for the value selected in the slider and return the values that are equal to 1. For example, if "A" is selected, it should filter as X[A] = 1, and if "E" is selected, it should select those where X[E] = 1. In other words, I want to dynamically change the column name. What can I do for this?

[_elbpower]

Use calculated Groups to do that.

Create a calculated group called "Risk CG",

Within that Group create items A, B, C and so on 

and return a filter from each of the items using a specific column eg: FILTER(DB_Table, A = 1)

Then use this calculated Group column in the slicer. 

[_elbpower]

FilteredTableByRiskType =
VAR SelectedRisk = SELECTEDVALUE('Risk_Table'[Risk])
RETURN
FILTER(
    DB_Table,
    (
        SelectedRisk = "A" && DB_Table[A] = 1 ) ||
        (SelectedRisk = "B" && DB_Table[B] = 1) ||
        (SelectedRisk = "C" && DB_Table[C] = 1) ||
        (SelectedRisk = "D" && DB_Table[D] = 1)
    )
)

[Bkurtsatar]

I edited the code in the same way. But it still returned empty table.

This code return empty table:

FilteredTableByRiskType =
VAR SelectedRisk = SELECTEDVALUE('Risk_Table'[Risk])
RETURN
FILTER(
    DB_Table,
    (
        SelectedRisk = "A" && DB_Table[A] = 1 ) ||
        (SelectedRisk = "B" && DB_Table[B] = 1) ||
        (SelectedRisk = "C" && DB_Table[C] = 1) ||
        (SelectedRisk = "D" && DB_Table[D] = 1)
    )
)

But this code return correct data: 

FilteredTableByRiskType =
VAR SelectedRisk = SELECTEDVALUE('Risk_Table'[Risk])
RETURN
FILTER(
    DB_Table,
    (
        SelectedRisk = "A" && DB_Table[A] = 1 ) ||
        (SelectedRisk = "B" && DB_Table[B] = 1) ||
        (SelectedRisk = "C" && DB_Table[C] = 1) ||
        (SelectedRisk = "D" && DB_Table[D] = 1)
    )
)