Possible to hide specific values in a table? Conditional Formatting?

[ERing]

I recently built a simple report like below and my stakeholder for the report would like to hide specific values in the report.

Currently, I'm using a text box to hide the values, but the text boxes will be in the wrong place is a user changes the sorting.

Is there anyway I can hide specific values in a table? It doesn't have to show "N/A", although that would be preferred.

Sample File 

[OwenAuger · Accepted solution]

Hi @ERing 

A nice method here might be to use a calculation group to override the measure values.

I've attached an updated PBIX.

1. Create a calculation group called 'Measure Display' with a calculation item column 'Measure Display Option', containing a calculation item 'Hide Specific Values'.

The expression for this calculation item could be:

VAR MeasureChannelToHide =
    {
        ( "Web Sessions", "Display" ),
        ( "Web Sessions", "Organic" ),
        ( "Web Sessions to Web Initiations Conversion Baseline", "Organic" )
    }
VAR CurrentMeasureName = SELECTEDMEASURENAME ( )
VAR CurrentChannel = SELECTEDVALUE ( 'Channel Mapping'[Channel] )
VAR Result =
    IF (
        AND (
            ISINSCOPE ( 'Channel Mapping'[Channel] ),
            ( CurrentMeasureName, CurrentChannel ) IN MeasureChannelToHide
        ),
        "N/A",
        SELECTEDMEASURE ( )
    )
RETURN
    Result

You may want to adjust or remove the ISINSCOPE condition. As it stands, it will only hide the measure on the row of the specific Channel, not the total row (in case a single Channel is filtered).

2. Apply this calculation item as a filter on the required visuals/pages/report.

Is this the sort of thing you were looking for?

[ajaybabuinturi · Accepted solution]

Hi @ERing,

I would like to suggest you to tweak your measures as follows that will achieve your requirement.

Web Sessions1 = IF(SELECTEDVALUE('Channel Mapping'[Channel]) IN {"Display", "Organic"}, //Add required channels
BLANK(), //True Result. If you need any other Text, that will be as "Test"  
sum(Web_Sessions[Web Sessions])) //False Result

Web Sessions to Initiations % = 
Var Result = DIVIDE('Measures Table'[Web Initiations Baseline],'Measures Table'[Web Sessions Baseline], BLANK())

vAR Final_Result = IF(SELECTEDVALUE('Channel Mapping'[Channel]) = "Organic", //Add required channels
BLANK(), //True Result. If you need any other Text, that will be as "N/A"  
Result) //False Result

RETURN
Final_Result

Click here to download .pbix file 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[OwenAuger · Accepted solution]

Hi @ERing 

A nice method here might be to use a calculation group to override the measure values.

I've attached an updated PBIX.

1. Create a calculation group called 'Measure Display' with a calculation item column 'Measure Display Option', containing a calculation item 'Hide Specific Values'.

The expression for this calculation item could be:

VAR MeasureChannelToHide =
    {
        ( "Web Sessions", "Display" ),
        ( "Web Sessions", "Organic" ),
        ( "Web Sessions to Web Initiations Conversion Baseline", "Organic" )
    }
VAR CurrentMeasureName = SELECTEDMEASURENAME ( )
VAR CurrentChannel = SELECTEDVALUE ( 'Channel Mapping'[Channel] )
VAR Result =
    IF (
        AND (
            ISINSCOPE ( 'Channel Mapping'[Channel] ),
            ( CurrentMeasureName, CurrentChannel ) IN MeasureChannelToHide
        ),
        "N/A",
        SELECTEDMEASURE ( )
    )
RETURN
    Result

You may want to adjust or remove the ISINSCOPE condition. As it stands, it will only hide the measure on the row of the specific Channel, not the total row (in case a single Channel is filtered).

2. Apply this calculation item as a filter on the required visuals/pages/report.

Is this the sort of thing you were looking for?

[ERing]

@OwenAuger  Yes, this is what I was looking for.  Would it be possible to add red/green formatting these values.

For example, I'd like to show Web Session (values not displayed as N/A) in green if above 500,000 and Red if below 500,000. Similarly I'd like to show Web Sessions to Initiations  % (values not displayed as N/A) in green if 17% or above and in red if below 17%.

Does this seem possible?

Thank you

[OwenAuger]

@ERing - glad to hear it! 🙂

Sure, here are a couple of options for conditional formatting (PBIX attached):

1. Use Conditional Formatting Rules

These rules will automatically not apply to the "N/A" measure values:

2. Create measure returning the colours:

With this method, it makes more sense to load the Measure/Channel combinations to a physical table (which can also be referenced by the calculation group):

Then create these measures to return the colour:

Colour Web Sessions = 
VAR Threshold = 500000
VAR ColourHigh = "#88E769"
VAR ColourLow = "#DA898F"
VAR CurrentChannel = SELECTEDVALUE ( 'Channel Mapping'[Channel] )
VAR HiddenChannels =
    CALCULATETABLE (
        VALUES ( 'Hidden Measure Channel'[Channel] ),
        'Hidden Measure Channel'[Measure] = "Web Sessions"
    )
    
VAR Colour =
    IF (
        NOT CurrentChannel IN HiddenChannels,
        VAR MeasureValue = [Web Sessions]
        RETURN
        SWITCH (
            TRUE (),
            MeasureValue >= Threshold, ColourHigh,
            ColourLow
        )
    )
RETURN
    Colour

Colour Web Sessions to Initiations % = 
VAR Threshold = 0.17
VAR ColourHigh = "#88E769"
VAR ColourLow = "#DA898F"
VAR CurrentChannel = SELECTEDVALUE ( 'Channel Mapping'[Channel] )
VAR HiddenChannels =
    CALCULATETABLE (
        VALUES ( 'Hidden Measure Channel'[Channel] ),
        'Hidden Measure Channel'[Measure] = "Web Sessions to Web Initiations Conversion Baseline"
    )
VAR Colour =
    IF (
        NOT CurrentChannel IN HiddenChannels,
        VAR MeasureValue = [Web Sessions to Web Initiations Conversion Baseline]
        RETURN
        SWITCH (
            TRUE (),
            MeasureValue >= Threshold, ColourHigh,
            ColourLow
        )
    )
RETURN
    Colour