Need to change column values in visual based on slicer (that has the 3 column names)

[newbie_pbi]

Hello

I have a big table (Table A) in import mode with 80+column names. This table has 3 columns named-"Name 1", "Name 2", "Name 3". There is a column of "Ids"

Table A like this:

Ids	Name 1	Name 2	Name 3	another 75+ columns
1	Apple	hello	do
2	Apple	Hi	put
3	Ball	bat	do
4	Cat	make	take
5	Zebra	gol	take
6	Zebra	gol	back

Now I need to have a visual where a slicer will have those 3 options-Name 1, Name 2, Name 3. When Name 1 is slelected a visual table will show values from column-Name 1 along with the other required columns. When Name 2 is selected instead of Name 1 Values, Name 2 values should be shown while rest of the columns remain the same. When Name 3 is slected only Name 3 values should be shown besides the other columns.

For the slicer I created a table called 'Slicer' with 1 column called NAME column it has 3 rows with strings-Name 1, Name 2 and Name 3. This table is just for the slicer, no relationships.

NAME

Name 1

Name 2

Name 3

For changing the column in the visual depending on the visual I have a measure (without Min or Max it was not working):

name_measure =

SWITCH(SELECTEDVALUE('Slicer'[NAME]),

        "Name 1", MAX(Table A [Name 1]).,

        "Name 2", MAX(Table A[Name 2]),

        "Name 3", max(Table A[Name 3])

)

So when slicer has Name 1 the visual table is as follows:

name_measure	column A	column B	column c
Apple	2	A	er
Apple	12	G	tr
Ball	3	B	bt
Cat	4	C	yh
Zebra	5	D	bt
Zebra	6	D	bt

When Slicer name is changed to Name 2:

name_measure	column A	column B	column c
hello	2	A	er
Hi	12	G	tr
bat	3	B	bt
make	4	C	yh
gol	5	D	bt
gol	6	D	bt

I thought it was working.

But then I needed another visual where I am counting number of ids. And when I select each of the options in the slicer, I just get the Max value which is a name starting with "Z". So for Name 1 option I get
like this:

name_measure	count of ids
Zebra	6

But I need to bring in all values from Name 1 column like this when Name 1 is selected.

name_measure	count of ids
Apple	2
Ball	1
Cat	1
Zebra	2

Then when Name 2 is selected the same visual should change to

name_measure	Count of ids
hello	1
Hi	1
bat	1
make	1
gol	2

 and not:

name_measure	Count of ids
make	6

Any idea how can I change the name_measure so I can change and get all values for each of those columns in a visual table depending on what column name the user selected in  the slicer?

Thanks!

[newbie_pbi]

Thanks for the reply. But how do I union the 2 tables? Slicer table has just 1 column with 3 rows. Table A has more than 2 million rows and 80+ columns. And the 3 rows in the slicer table are 3 columns in Table A.

[Shravan133]

Hi,

Try to adjust your measure so that it evaluates each row based on the slicer selection. The solution involves using the UNION and SELECTCOLUMNS functions to create a unified table that responds to the slicer selection.

Maybe try this approach:

Step 1: Create the Dynamic Measure

You need to create a measure that evaluates the selected column values dynamically:

name_measure =

VAR selectedColumn = SELECTEDVALUE('Slicer'[NAME])

VAR resultTable =

    SWITCH(

        selectedColumn,

        "Name 1", SELECTCOLUMNS('Table A', "Name", 'Table A'[Name 1]),

        "Name 2", SELECTCOLUMNS('Table A', "Name", 'Table A'[Name 2]),

        "Name 3", SELECTCOLUMNS('Table A', "Name", 'Table A'[Name 3])

    )

RETURN

    resultTable

Step 2: Create a Measure for Counting IDs

To count the number of IDs based on the selected column, you need to create another measure:

CountOfIDs =

VAR selectedColumn = SELECTEDVALUE('Slicer'[NAME])

VAR resultTable =

    SWITCH(

        selectedColumn,

        "Name 1", SELECTCOLUMNS('Table A', "Name", 'Table A'[Name 1], "ID", 'Table A'[Ids]),

        "Name 2", SELECTCOLUMNS('Table A', "Name", 'Table A'[Name 2], "ID", 'Table A'[Ids]),

        "Name 3", SELECTCOLUMNS('Table A', "Name", 'Table A'[Name 3], "ID", 'Table A'[Ids])

    )

RETURN

    COUNTROWS(

        SUMMARIZE(

            resultTable,

            [Name],

            "CountOfIDs", COUNT([ID])

        )

    )

Step 3: Use These Measures in Your Visuals

1. Table Visual:
• Add the name_measure as a column in your table visual.
• Add other required columns from Table A.
2. Count of IDs Visual:
• Add the CountOfIDs measure in a card or table visual to show the count of IDs.

Example of Usage in a Table Visual:

1. Create a table visual:
• Add name_measure to the visual.
• Add other necessary columns from Table A.
2. Create a visual for counting IDs:
• Add name_measure as a column.
• Add CountOfIDs measure to show the count of IDs.

This approach ensures that the values and counts are dynamically adjusted based on the slicer selection. The use of SELECTCOLUMNS and SWITCH ensures that you get the correct column values in your visuals based on user interaction with the slicer.