Slicer by columns name

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

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

MFelix Thank you so much. This really helped me.

Hi MFelix,
Thank you for the reply.

I tried that option but report with field parameter can't be uploaded to powerbi report server with that version.
If there is any other way, pls let me know.