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

Slicer by columns name

Hello,

 

I have the following table:

 

 

Sale_IDUnity_PriceTax_1Tax_2Tax_3Tax_4
11051584
240302587


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_PriceTax_1Tax_2Tax_3Tax_4

 

Table Visual --

Sale_IDFinal_Price
118
280

 

If I click on Unity_Price the it must shows this:

 

Slicer -- 

Unity_PriceTax_1Tax_2Tax_3Tax_4

 

Sale_IDFinal_Price
110
240

 

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

 

Slicer -- 

Unity_PriceTax_1Tax_2Tax_3Tax_4

 

Sale_IDFinal_Price
18
240

 

 

How can I do that?

Thanks.

1 ACCEPTED SOLUTION
MFelix
Super User
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. slicer.png
  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:

resut.png

 

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



View solution in original post

20 REPLIES 20
MFelix
Super User
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. slicer.png
  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:

resut.png

 

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 ?"

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

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

@MFelix 

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?

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:

BadgeIDJob TitleCountryFirstNameLastNameBoothNumberBooth NameDuration Seconds
123EngineerUSAabcWoods27Booth 1600
456EngineerGermanydefWoods27Booth 1480
789EngineerIndiaghiSa27Booth 11320
532Chief Information OfficerUSAjklVa14Booth 2240
1182304Operations ManagerUSAChetManchester21Booth 3980
1182523Account ManagerUSATravisFulton21Booth 31080
1182657Senior EngineerIndiaLuisaSangines21Booth 31680
1182879EngineerUSAMarkSkallet21Booth 3480
1149995AnalystUSAMeganHemmila37Booth 41200
1149995AnalystIndiaMeganHemmila37Booth 42280
1150180Architect|PartnerIndiaHoaTram37Booth 41200
1150180Architect|PartnerGermanyHoaTram37Booth 4480
117Senior EngineerUSApqrPa14Booth 2360

 

 

Slicer by Column Name.png

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.

 

charts.gif

 

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



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

Thank you @MFelix 

Hi @Keelin,

 

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

 

Smiley HappySmiley HappySmiley HappySmiley HappySmiley Happy

 

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 New TableResults from Main TableResults from Main Table

 

 

 

 

 

 

 

 

Thanks in advance

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:

 

Top5 in Unpivot.png

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!

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

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 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors