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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

32 REPLIES 32

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



Anonymous
Not applicable

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

Thank you @MFelix 

Hi @Anonymous,

 

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



Hi MFelix,

I want to create a slicer of multiple columns based on another slicer having parameters.

for example raw data is like this:

EventStudent IDMaths teacherScience TeacherEnglish Teacher
Exhibition1Ravi ChandraSpriha DasVandana p
Exhibition2RK Das Mamta Chobey
Exhibition3 Rajesh RanjanRajesh Ranjan

 

So first slicer should have values like Maths, Science and English:

Slicer 1
Maths
Science
English

 

And second slicer should filter based on Slicer 1 for example if Maths is selected in slicer 1 , then slicer 2 should have following values:

Slicer 2
Ravi Chandra
RK Das

 And table should show this:

EventStudent IDMaths teacher
Exhibition1Ravi Chandra
Exhibition2RK Das

 

I tried with field parameters but it cant be used in report server

Please help me with this.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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