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

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

HI Anonymous ,

Based on the image you are showing the best option is to unpivot you data to get a single column for values and a single colunm for the brands something similar to this:

Full code for Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYyxDcAwCAR3+dqFia0YZkHsv0ZypEjzOnH/ZMo05PcbNq9OJ22qRopLID3+2qcWCu9NrHy32SBvD3QYxVLVAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CW = _t, Mercedes = _t, BMW = _t, Audi = _t, VW = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CW", Int64.Type}, {"Mercedes", Int64.Type}, {"BMW", Int64.Type}, {"Audi", Int64.Type}, {"VW", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CW"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

The you can use values on Y Axis and the Attribute on the legend.