Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
newbie_pbi
New Member

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

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:

IdsName 1Name 2Name 3another 75+ columns
1Applehellodo 
2AppleHiput 
3Ballbatdo 
4Catmaketake 
5Zebragoltake 
6Zebragolback 


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_measurecolumn Acolumn Bcolumn c
Apple2Aer
Apple12Gtr
Ball3Bbt
Cat4Cyh
Zebra5Dbt
Zebra6Dbt


When Slicer name is changed to Name 2:
name_measurecolumn Acolumn Bcolumn c
hello2Aer
Hi12Gtr
bat3Bbt
make4Cyh
gol5Dbt
gol6Dbt

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_measurecount of ids
Zebra6

But I need to bring in all values from Name 1 column like this when Name 1 is selected.
name_measurecount of ids
Apple2
Ball1
Cat1
Zebra2

Then when Name 2 is selected the same visual should change to
name_measureCount of ids
hello1
Hi1
bat1
make1
gol2
 and not:
name_measureCount of ids
make6

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!
2 REPLIES 2
newbie_pbi
New Member

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
Solution Sage
Solution Sage

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.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.