cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## Slicer by columns name

Hello,

I have the following table:

 Sale_ID Unity_Price Tax_1 Tax_2 Tax_3 Tax_4 1 10 5 15 8 4 2 40 30 25 8 7

To calculate the final price of a sale I created a calcualted column with the following formula:

Final_Price = Unity_Price + Tax_1 + Tex_2 - Tax_3 - Tax_4

Now I want to filter the final price by my columns names:

Slicer --

 Unity_Price Tax_1 Tax_2 Tax_3 Tax_4

Table Visual --

 Sale_ID Final_Price 1 18 2 80

If I click on Unity_Price the it must shows this:

Slicer --

 Unity_Price Tax_1 Tax_2 Tax_3 Tax_4

 Sale_ID Final_Price 1 10 2 40

Or if I multiselect items, all taxes for example, it must shows this:

Slicer --

 Unity_Price Tax_1 Tax_2 Tax_3 Tax_4

 Sale_ID Final_Price 1 8 2 40

How can I do that?

Thanks.

1 ACCEPTED SOLUTION
Super User

Hi @Anonymous,

I would solve this using a measure do the following:

1. Create a table (do not related this with any other tables with the following structure:
1. Name Slicer - Selection: Unity_Price, Tax_1, Tax_2, Tax_3, Tax_4
2. Add the following measure to your data table:
3. ```Final_Price =
VAR Unity_price =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Unity_Price" ) = TRUE (),
SUM ( Sales[Unity_Price] ),
0
)
VAR Tax1 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_1" ) = TRUE (),
SUM ( Sales[Tax_1] ),
0
)
VAR Tax2 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_2" ) = TRUE (),
SUM ( Sales[Tax_2] ),
0
)
VAR Tax3 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_3" ) = TRUE (),
SUM ( Sales[Tax_3] ),
0
)
VAR Tax4 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_4" ) = TRUE (),
SUM ( Sales[Tax_4] ),
0
)
RETURN
Unity_price + Tax1
+ Tax2
- Tax3
- Tax4```
4. Now just add the valuies from the table you created to a slicer and your measure to the table should give the expected result:

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

20 REPLIES 20
Super User

Hi @Anonymous,

I would solve this using a measure do the following:

1. Create a table (do not related this with any other tables with the following structure:
1. Name Slicer - Selection: Unity_Price, Tax_1, Tax_2, Tax_3, Tax_4
2. Add the following measure to your data table:
3. ```Final_Price =
VAR Unity_price =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Unity_Price" ) = TRUE (),
SUM ( Sales[Unity_Price] ),
0
)
VAR Tax1 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_1" ) = TRUE (),
SUM ( Sales[Tax_1] ),
0
)
VAR Tax2 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_2" ) = TRUE (),
SUM ( Sales[Tax_2] ),
0
)
VAR Tax3 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_3" ) = TRUE (),
SUM ( Sales[Tax_3] ),
0
)
VAR Tax4 =
IF (
CONTAINS ( Slicer, Slicer[Selection], "Tax_4" ) = TRUE (),
SUM ( Sales[Tax_4] ),
0
)
RETURN
Unity_price + Tax1
+ Tax2
- Tax3
- Tax4```
4. Now just add the valuies from the table you created to a slicer and your measure to the table should give the expected result:

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hi Miguel

I am trying to achieve the same thing with Strings instead of Taxes

Please see the below sample code and assist me on the same

This is what i tried but not succesful

Verification Type =
VAR ThirdPartyAudit =
IF (
CONTAINS ( Slicer, Slicer[Selection], "abcd" ) = TRUE (),
"abcd" ),
0
)
VAR InCountryVisit =
IF (
CONTAINS ( Slicer, Slicer[Selection], "efgh" ) = TRUE (),
"efgh",
0
)
VAR Remote =
IF (
CONTAINS ( Slicer, Slicer[Selection], "pqrs") = TRUE (),
"pqrs",
0
)
VAR STP=
IF (
CONTAINS ( Slicer, Slicer[Selection], "xyz" ) = TRUE (),
"xyz",
0
)
RETURN
"what should i write here ?"

Super User

Hi @Anonymous,

In this case and since you are using text you need to use the & between the several values instead of the +.

However be advise that if you are using text you don't want to return 0 if the value does not exists maybe you should change it to blank.  Check the code below.

Verification Type =
VAR ThirdPartyAudi =
IF (
CONTAINS ( Slicer, Slicer[Selection], "abcd" ) = TRUE (),
"abcd" ),BLANK()
)
VAR InCountryVisit =
IF (
CONTAINS ( Slicer, Slicer[Selection], "efgh" ) = TRUE (),
"efgh",BLANK()
)
VAR Remote =
IF (
CONTAINS ( Slicer, Slicer[Selection], "pqrs") = TRUE (),
"pqrs",
BLANK()
)
VAR STP=
IF (
CONTAINS ( Slicer, Slicer[Selection], "xyz" ) = TRUE (),
"xyz",
BLANK()
)
RETURN
Third Party Audit & InCountryVisit & Remote & STP

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Thanks @MFelix

However I am not getting any values in my column

Super User

Hi @Anonymous

What is the format of your data? Do you have a table with the text or you just simply want to to a concatenation of the text based on the slicer selections?

If it's the last one just do

``CONCATANEX(Slicer, Slicer[Selection], ",")``

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hey @MFelix

No concatenation Required,
Say i have 4 cols - the one i have used above

IF of them have text values as Yes/No

Which ever is 'YES' of the 4, i need to show it as Verification Type

Anonymous
Not applicable

I have the following Master table where "Monday7","Tuesday7","Monday30","Tuesday30" represent the Utilization percentage of corresponding product name. For example: "Monday30" represent the utilization percentage of "Water Purifier" of all Mondays in past 30 days.

Similarly there are 4 categories (7 days, 30 days, 60 days, 90 days) where for each category, a separate columns will be present for all days of the week.

Name               Monday7      Tuesday7       Monday30    Tuesday30
Water Purifier       43                 44                   64                  32
Refrigerator          54                 12                   32                  45

Now i need to create two slicers, one to select the category ( "7 days","30 days", "60 days", "90 days") and second one to select the day of the week ("Mon","Tue","Wed","Thur","Fri","Sat", "Sund")

Now once users selects an option from both the slicers, I wanted to show all the visualizations in that page with the following data where the new column Utilization percent should show the data same as "Monday7" column if user selects "7 days" and "Monday" in slicer.

Name              UtilizationPercent
Water Purifier            43
Refrigerator               54

Is this possible? can you please guide me?

Advocate I

Can't thank you enough for this solution! Worked for me perfectly!

Anonymous
Not applicable

Hi @MFelix I have similar issue and i want to create a slicer where column name will be slicer and when i click on slicer it should change the data in teh donut.

Please see the screenshot below where is have shown my whole data. I am not sure where to upload PBIx file and attaching screeshot here.. Here is the table:

 BadgeID Job Title Country FirstName LastName BoothNumber Booth Name Duration Seconds 123 Engineer USA abc Woods 27 Booth 1 600 456 Engineer Germany def Woods 27 Booth 1 480 789 Engineer India ghi Sa 27 Booth 1 1320 532 Chief Information Officer USA jkl Va 14 Booth 2 240 1182304 Operations Manager USA Chet Manchester 21 Booth 3 980 1182523 Account Manager USA Travis Fulton 21 Booth 3 1080 1182657 Senior Engineer India Luisa Sangines 21 Booth 3 1680 1182879 Engineer USA Mark Skallet 21 Booth 3 480 1149995 Analyst USA Megan Hemmila 37 Booth 4 1200 1149995 Analyst India Megan Hemmila 37 Booth 4 2280 1150180 Architect|Partner India Hoa Tram 37 Booth 4 1200 1150180 Architect|Partner Germany Hoa Tram 37 Booth 4 480 117 Senior Engineer USA pqr Pa 14 Booth 2 360

Super User

Hi @Anonymous,

You can do this making  of 3 options

1. Dynamic Hierarchie
2. Unpivot Columns
3. Bookmarks

1. Dynamic Hierarchie

Using this post I adapted the situation and made the following

• Created a new table with the following code:

```Country Job Hierarchy =
UNION (
SELECTCOLUMNS (
'Hierarchy';
"Badge ID"; 'Hierarchy'[BadgeID];
"Hierarchy Name"; 'Hierarchy'[Job Title];
"Level"; "Job Title";
"LevelNumber"; 1
);
SELECTCOLUMNS (
'Hierarchy';
"Badge ID"; 'Hierarchy'[BadgeID];
"Hierarchy Name"; 'Hierarchy'[Country];
"Level"; "Country";
"LevelNumber"; 2
)
)```

j

• Make a relationship between this table and your main table by BadgeID: be aware that this will create a many to many relationship. If you don't have the latest version of PBI you need to make a dimension table for Uniques BadgeID and then relate this with the two other table
• Make this relationship with a cross filter in both directions
• Add the Column Hierarchy name to your legend and the count of what ever field you want from the main table.

2. Unpivot Column (link to unpivot columns)

• Query Editor Select both country and Job title column
• Transform Unpivot
• Get 2 columns atribute and value
• Add Attribute to slicer and  value to legend in chart

3. Bookmarks (link to bookmarks documentation)

• Create two buttons Job title Country
• Create two donut charts one with job title and the cother with country on legend
• Hide the Job Title chart and create the bookmark named country and make it an action of the Country button and vice-versa
• Then just click on the buttons (on desktop version you have to use CTRL + CLICK)

See below the screenshot and the PBIX file with all the options above.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper IV

T.H.I.S.I.S.A.W.E.S.O.M.E!!

Thank you @MFelix

Super User

Hi @Keelin,

Don't know how I could help with this post but glad it serves your purposes.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hi @MFelix ,

I am trying to achieve something similar and I really like the Dynamic hierarchies option and is also suitable for my requirement. I have created a new table using the DAX query and joined with the main table, the totals in the two result sets is same but the breakdown is not right. I am not sure why this is happening, please tell me if I am missing anything.

Results from New TableResults from Main Table

Thanks in advance

Super User

HI @Anonymous ,

Without any specification and data about your model is difficul to give you an answrr, but have you checked if you hierarchy model is linked in a many to many relationship and with cross filtering active?

Can you share your file? If information is sensitive do a mockup file or shared it trough private message.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

HI @MFelix, Thats superb!,

I liked you have posted 3 methods and i found Unpivot is easy for me since I am not expert in Dax code. I have used your method in my table using Unpivot and everthing looks great except i have one more requirment which i forgot to mention in previous post.

1) I want to show the Top 5 values in the chart, I tried to add Visual level filter but nothing happened.

2) I saw that Donut Chart is showing value for Both Attribute in single chart but I want to show either Country or JobTitle only at the same time. Can we select one value by default or is there any other method to achive the same? In the real data I have 7 attributes that need to be shown in one donut chart.

Please see the attached screenshot:

I found that Bookmark mehtod is good if we don't have any other slicer in the chart but when we use multiple slicer that change the chart Bookmark method is failed.

Thank you so much for the detail answer.

Kulchandra

Anonymous
Not applicable

Hey Meflix,

I've almost similar issue but I'm not able to resolve it the way you mentioned.

Following are the steps

• Slicer with four different column names
• Select a column name
• Divide the values in column with values in another column present in data
• Use this measure on the graph

I tried following the code you mentioned but instead of Sum(Sales[VAR]) I want Divide(VAR, Column Name)

The code is returning a lot of errors

ETA_Quality =
VAR On_Time =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
Divide( 'Volvo ETA'[On-Time], 'Volvo ETA'[Total], Blank()),
0
)
VAR Early =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Early" ) = TRUE (),
Divide( 'Volvo ETA'[Early], 'Volvo ETA'[Total], Blank()),
0
)
VAR Late =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Late" ) = TRUE (),
Divide( 'Volvo ETA'[Late], 'Volvo ETA'[Total], Blank()),
0
)
VAR Cancelled =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Cancelled" ) = TRUE (),
CALCULATE( 'Volvo ETA'[Cancelled] 'Volvo ETA'[Total], Blank()),
0
) Return Cancelled+Early+Late+On_Time

Also instead of last Return Cancelled+Early+Late+On_Time, I just want to Return all of them which will eventually display the VAR value based on Column Selection.

Thanks!

Super User

Hi @Anonymous,

When you use measure they are calculated based on context so you cannot make the calculations based on a single value from a colum, meaning that when you have

```VAR On_Time =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
Divide( 'Volvo ETA'[On-Time], 'Volvo ETA'[Total], Blank()),
0
)```

You should have something like this:

```VAR On_Time =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
DIVIDE ( SUM ( 'Volvo ETA'[On-Time] ), SUM ( 'Volvo ETA'[Total] ), BLANK () ),
0
)```

In this way you are aggregating the values, however be carefull because this can sometimes based on context not return the expected result.

Can you please share some sample data and show the expected result in that way I can adjust the formula better.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Hey @MFelix ,

Thanks for the reply. I realised the issue now and almost fixed it. Also what are you tryiig to say in the first step? I created a different table with all the four columns in it named Early, Late, Cancelled, On Time but I cannot still add the slicer which gives me all column names in the selection. Can you explain the first step a little more?

It's giving me this error:

Failed to resolve name 'Slicer'. It is not a valid table, variable, or function name.

Thanks!

Niket

Super User

Hi @Anonymous,

In the previous post the name of the table was Slicer that's why it's in the formula.

You need to place the name of the table you used to make the slicer selection.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Thank you, @MFelix, it solved my problem.

## Helpful resources

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

#### Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors
Top Kudoed Authors