Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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...
Need Help
Show and Tell
Tutorial Requests
  • MFelix's avatar
    MFelix
    9 years ago

    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

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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