Display dimension in tooltip based on slicer selection

[fibaek]

Hello,

I have a report based on a live connection to a PBI Dataset. In the report I have a clustered column chart with a tooltip that displays further details. There is a slicer on the page that allows the user to filter the data based on three dimensions with a logical hierarchical relation between them (Market -> BU -> SBU). Is no values is selected in the slicer the user should see markets in the tooltip, if a market is selected BUs should be shown etc. The Market/BU/SBU dimensions are currently not included in the visual, but can be added to the axis if needed.

I can use one of the many solutions to switch axis on a visual based on a user selection, however they usually require two additional tables. Although I can add tables to the connected dataset I would prefer not to in order to avoid 'polluting' the dataset with tables that are only relevant to one report. I would also prefer not to convert the connection to direct query/mixed mode if at all possible.

Is there a way to solve this?

Any help is much appreciated!

/Sune

[fibaek]

Hello @MFelix,

Thank you once again! Your solution is close to being exactly what I am trying to do. The users will typically select a region – either directly through the slicer or be restricted through RLS – and then potentially one or more BUs. In case of no selection the user should see the regions – not the SBUs - in the table, and if both region and BU is selected it should act as if only BU is selected. But other than that it is absolutely perfect! I have tried playing around with some logical NOTs but haven’t quite been able to nail it because BU is included in the tooltip. Any input would be highly appreciated!

It seems like the measure for formatting is not needed? I just added another measure and did conditional formatting on it. Looks just fine.

Link to .pbix on OneDrive: https://1drv.ms/u/s!AmZ2f-AI5Q3zgYsQLvmSZyPrF_t1gg?e=Efpqre

Thanks,

/Sune

[fibaek]

Hi Miguel,

Thank you for your reply – your input is highly appreciated!

I have been playing around with you suggested solution, and it is definitely going in the right direction. However my tooltip is a table (screenshot below) with com calculated measures. The months in the screenshot are the ones that should ideally be region/bu/sbu. When I try to create a table it seems like I get all records in the selected dimension resulting in a long list. I’ll create a .pbix with some scrambled data tomorrow.

/Sune

[MFelix]

Hi @fibaek,

Looking at the information you are giving you need to the following:

• Change your measure to:

Filtering =
VAR selection_value =
    SWITCH (
        TRUE ();
        ISFILTERED ( 'Table'[Region] ); MAXX ( 'Table'; 'Table'[BU] );
        ISFILTERED ( 'Table'[BU] ); MAXX ( 'Table'; 'Table'[SBU] );
        ISFILTERED ( 'Table'[SBU] ); MAXX ( 'Table'; 'Table'[Region] )
    )
RETURN
    IF ( ISINSCOPE ( 'Table'[BU] ); selection_value; "Total" )

Once again order is very import 

• Create a table visualization for your tooltip with the following setup:
  • BU - Hide column
  • Filter Measure
  • Your measure
• Create a measure with the following code for condittional formatting:

Filtering colour =
SWITCH (
    TRUE ();
    ISFILTERED ( 'Table'[Region] ); "#00000000";
    ISFILTERED ( 'Table'[BU] ); "#00000000";
    "White"
)

• Added this step since I don't know what you want to present in the tooltip when you have no selection so I created this to "hide the tooltip chart"
• Create a card visualization and format the background and the data label with the filtering colour
• Place the can with the size and on top of your table visualization

Result below and in attach PBIX file:

[MFelix]

Hi @fibaek ,

Not really sure of what is the final result you want to have in your tooltip, but in my case what I did was simple card with the following syntax for the tooltip:

Filtering =
SWITCH (
    TRUE ();
    ISFILTERED ( 'Table'[BU] );
        CONCATENATEX (
            ALLSELECTED ( 'Table' );
            'Table'[SBU] & " - "
                & CALCULATE (
                    SUM ( 'Table'[Value] );
                    FILTER ( ALL ( 'Table'[SBU] ); 'Table'[SBU] IN VALUES ( 'Table'[SBU] ) )
                );
            UNICHAR ( 10 )
        );
    ISFILTERED ( 'Table'[Region] );
        CONCATENATEX (
            'Table';
            'Table'[BU] & " - "
                & CALCULATE (
                    SUM ( 'Table'[Value] );
                    FILTER ( ALL ( 'Table'[BU] ); 'Table'[BU] IN VALUES ( 'Table'[BU] ) )
                );
            UNICHAR ( 10 )
        )
)

The trick here is the ISFILTERED part. Can you please tell what type of tooltip you want to display? 

Also be aware of rthe order of the syntax otherwise the result will be incorrect.

[fibaek]

Hello @MFelix  ,

Thank you once again! Your solution is close to being exactly what I am trying to do. The users will typically select a region – either directly through the slicer or be restricted through RLS – and then potentially one or more BUs. In case of no selection the user should see the regions – not the SBUs - in the table, and if both region and BU is selected it should act as if only BU is selected. But other than that it is absolutely perfect! I have tried playing around with some logical NOTs but haven’t quite been able to nail it because BU is included in the tooltip. Any input would be highly appreciated!

It seems like the measure for formatting is not needed? I just added another measure and did conditional formatting on it. Looks just fine.

Link to .pbix on ondrive: https://1drv.ms/u/s!AmZ2f-AI5Q3zgYsQLvmSZyPrF_t1gg?e=9AYQEl

Thanks,

/Sune

[MFelix]

Hi @fibaek ,

I have one question regarding the information you have place in the PBIX file.

You refer everything is working properly however I'm always seeing the same filtering in your visualization that don't match your request.

What do you need me to help you with, I have to be honest I got lost about what you need.