Help needed with the Visualization of Selection vs Others

[huguestremblay]

Hello,

I need to create various visuals where the user will select what they want to include (from 3 fields) and will get a visual that shows them the results for their selection and another for all other records.  I created a simple report to demonstrate - available here.

The user makes a selection using the Slicer GCS, Slicer CQR and Client Harmonized slicers (on the left) and get the resulting table and/or chart.

I am using a Measure that I created based mostly on this post and the solution proposed by @v-frfei-msft (thank you!) , using the following Measure:

Measure =

VAR seleGCS = SELECTEDVALUE ( 'TableGCS'[slicer GCS] )

VAR seleCQR = SELECTEDVALUE ('TableCQR'[Slicer CQR])

VAR SeleSponsor = DISTINCT ( 'TableClient'[Client Harmonized])

RETURN

IF (

ISFILTERED ('TableClient'[Client Harmonized])

&& COUNTROWS ( INTERSECT ( SeleSponsor, VALUES ( 'Sponsors'[Client Harmonized] ) ) ) > 0,

1,

IF (

ISFILTERED ( 'TableGCS'[slicer GCS] )

&& MAX ( 'GCS'[Is GCS] ) = seleGCS,

1,

IF (

ISFILTERED ( 'TableCQR'[slicer CQR] )

&& MAX ( 'CQR'[Is CQR] ) = seleCQR,

1, 0

)))

I am filtering my visulas with Measure = 1 or Measure = 0.  The Measure is providing the desired output when I have a Table visual, but is not working with a Chart.

Any help to make this work with my chart would be greatly appreciated.

Hugues.

[huguestremblay]

Thank you so much for all of your help.  It is not quite perfect but I will go with that for now.  I realize that what I would like to achieve is a lot more complex than I initially thought, and transfering your solution to my "real" data model will be enven more complex.  Again, a great thanks for everything you provided.

[huguestremblay]

This is so close.  The Selected Clients work perfectly but Others should return values when Slicer GCS and/or Slicer CQR is selected but the table and chart are blank.  In th example below, it shoud contain all of the clients that do not have a Yes in Is GCS (Clients C, D, E).

If there is a selection in Slicer GCS and/or Slicer CQR as well as in Client Harmonized, the Others side should return all values that do not meet both criteria.  In the example below, Client C values are missin from Others.

Regards,

Hugues.

[MFelix]

Hi @huguestremblay ,

Try the following change on the others:

Other Clients = 
VAR temp_table =
    SUMMARIZE (
        Sponsors,
        Sponsors[Client Harmonized],
        CQR[Is CQR],
        GCS[Is GCS],
        "CountColors", COUNT ( Main[Color] )
    )
RETURN
    IF (
        ISFILTERED ( TableClient[Client Harmonized] ),
        SUMX (
            FILTER (
                temp_table,
                NOT ( Sponsors[Client Harmonized] IN VALUES ( TableClient[Client Harmonized] ) )
                    && IF (
                        ISFILTERED ( TableCQR[Slicer CQR] ),
                        CQR[Is CQR] = BLANK (),
                        CQR[Is CQR] IN { "YES", BLANK () }
                    )
                    && IF (
                        ISFILTERED ( TableGCS[Slicer GCS] ),
                        GCS[Is GCS] = BLANK (),
                        GCS[Is GCS] IN { "YES", BLANK () }
                    )
            ),
            [CountColors]
        ),
        SUMX (
            FILTER (
                temp_table,
                IF (
                    ISFILTERED ( TableCQR[Slicer CQR] ),
                    CQR[Is CQR] = BLANK (),
                    CQR[Is CQR] IN { "YES", BLANK () }
                )
                    && IF (
                        ISFILTERED ( TableGCS[Slicer GCS] ),
                        GCS[Is GCS] = BLANK (),
                        GCS[Is GCS] IN { "YES", BLANK () }
                    )
            ),
            [CountColors]
        )
    )

[huguestremblay]

Hello @MFelix  and thank you for your help. 

Your Measure is better but still doesn't give me the expected result.

In your example screenshot, for Client C, the counts on the chart should be 1 yellow and 1 ornage (not 3 each).  You are also correct about the "Others" chart, the counts there should be 4 blue but also 2 Orange and 2 Yellow. The MAX is there from the example I used, Is GCS and Is CQR can only be blank or Yes.

Regards,

Hugues.

[MFelix]

Hi @huguestremblay ,

To what I can understand you want your charts to look like this:

This was done using a direct filter on the table, just for perception.

[huguestremblay]

Yes, this is what I am looking for.   To add some context, the user can select one of many Clients, and/or select Yes in either or both of the other 2 slicers.

[MFelix]

Hi @huguestremblay ,

I have made two different measures:

Selected Clients =
COUNTROWS (
    FILTER (
        SUMMARIZE ( MAIN, Main[ID], Sponsors[Client Harmonized] ),
        Sponsors[Client Harmonized] IN VALUES ( TableClient[Client Harmonized] )
    )
)


Other Clients =
COUNTROWS (
    FILTER (
        SUMMARIZE ( MAIN, Main[ID], Sponsors[Client Harmonized] ),
        NOT ( Sponsors[Client Harmonized] IN VALUES ( TableClient[Client Harmonized] ) )
    )
)

Has you can see below it's worknig has the tables. I lefted the two other measures and the tables just for comparision but there is no need to use these measures:

[huguestremblay]

The latest is working with the Client Harmonized slicer but does not account for the GCS and CQR slicers.

-Why is the "Other" chart updating without having any filter based on the Other Client Measure?

I have updated my example report as I realized that he CQR and GCS values were missing.  Your latest measures are on Page 3.

Is it possible to incorporate the other 2 slicers in the equation?

[MFelix]

Hi @huguestremblay ,

Use the following measures:

Selected Clients = 
VAR temp_table =
    SUMMARIZE (
        Sponsors,
        Sponsors[Client Harmonized],
        CQR[Is CQR],
        GCS[Is GCS],
        "CountColors", COUNT ( Main[Color] )
    )
RETURN
    SUMX (
        FILTER (
            temp_table,
            Sponsors[Client Harmonized]
                IN VALUES ( TableClient[Client Harmonized] )
                    && IF (
                        ISFILTERED ( TableCQR[Slicer CQR] ),
                        CQR[Is CQR] = "YES",
                        CQR[Is CQR] IN { "YES", BLANK () }
                    )
                    && IF (
                        ISFILTERED ( TableGCS[Slicer GCS] ),
                        GCS[Is GCS] = "YES",
                        GCS[Is GCS] IN { "YES", BLANK () }
                    )
        ),
        [CountColors]
    )


Other Clients = 
VAR temp_table =
    SUMMARIZE (
        Sponsors,
        Sponsors[Client Harmonized],
        CQR[Is CQR],
        GCS[Is GCS],
        "CountColors", COUNT ( Main[Color] )
    )
RETURN
    SUMX (
        FILTER (
            temp_table,
            NOT ( Sponsors[Client Harmonized] IN VALUES ( TableClient[Client Harmonized] ) )
                && IF (
                    ISFILTERED ( TableCQR[Slicer CQR] ),
                    CQR[Is CQR] = "YES",
                    CQR[Is CQR] IN { "YES", BLANK () }
                )
                && IF (
                    ISFILTERED ( TableGCS[Slicer GCS] ),
                    GCS[Is GCS] = "YES",
                    GCS[Is GCS] IN { "YES", BLANK () }
                )
        ),
        [CountColors]
    )

Result in attach PBIX page4.

[huguestremblay]

Sorry but still not quite as desired.  The "Selected" part works as intended, but not the "Other".  If the user selects Sclier GCS and/or Slicer CQR, the Others need to return records that don't have a Yes in those fields (not include din the Selection).

As an example, in my screenshot below, the Others" side shoud include all IDs that don't have a Yes in the Is GCS column (IDs 3, 4, 5, 8, 9, 10).

Also, my simplified report only has charts for colors but my real report has several other fields.  WIll I need individual measures for each of those fields that I want to chart?

Thanks again, you help is really appreciated.

[MFelix]

Hi @huguestremblay ,

Believe I have done the calculation has you need:

Selected Clients = 
VAR temp_table =
    SUMMARIZE (
        Sponsors,
        Sponsors[Client Harmonized],
        CQR[Is CQR],
        GCS[Is GCS],
        "CountColors", COUNT ( Main[Color] )
    )
RETURN
    SUMX (
        FILTER (
            temp_table,
            Sponsors[Client Harmonized]
                IN VALUES ( TableClient[Client Harmonized] )
                    && IF (
                        ISFILTERED ( TableCQR[Slicer CQR] ),
                        CQR[Is CQR] = "YES",
                        CQR[Is CQR] IN { "YES", BLANK () }
                    )
                    && IF (
                        ISFILTERED ( TableGCS[Slicer GCS] ),
                        GCS[Is GCS] = "YES",
                        GCS[Is GCS] IN { "YES", BLANK () }
                    )
        ),
        [CountColors]
    )

Other Clients = 
VAR temp_table =
    SUMMARIZE (
        Sponsors,
        Sponsors[Client Harmonized],
        CQR[Is CQR],
        GCS[Is GCS],
        "CountColors", COUNT ( Main[Color] )
    )
RETURN
    SUMX (
        FILTER (
            temp_table,
            NOT ( Sponsors[Client Harmonized] IN VALUES ( TableClient[Client Harmonized] ) )
                && IF (
                    ISFILTERED ( TableCQR[Slicer CQR] ),
                    CQR[Is CQR] = BLANK(), 
                    CQR[Is CQR]  IN { "YES", BLANK () }
                )
                && IF (
                    ISFILTERED ( TableGCS[Slicer GCS] ),
                    GCS[Is GCS] = BLANK(), 
                    GCS[Is GCS] IN { "YES", BLANK () }
                )
        ),
        [CountColors]
    )

Regarding you last question if you want the same logic you will need to make the calculation for the several values you need. It's the same if you were just adding columns to your visualization you could not add a single column to return values from other columns.

PBIX file attach.

[MFelix]

Hi @huguestremblay ,

This has to do with context, since the tables include the Sponsor, all works out properly however when you take that value from your context this does not return correct result.

If you change the measure to:

Measure =
VAR seleGCS =
    SELECTEDVALUE ( 'TableGCS'[slicer GCS] )
VAR seleCQR =
    SELECTEDVALUE ( 'TableCQR'[Slicer CQR] )
VAR SeleSponsor =
    DISTINCT ( 'TableClient'[Client Harmonized] )
RETURN
    CALCULATE (
        IF (
            ISFILTERED ( 'TableClient'[Client Harmonized] )
                && COUNTROWS (
                    INTERSECT ( SeleSponsor, VALUES ( 'Sponsors'[Client Harmonized] ) )
                ) > 0,
            1,
            IF (
                ISFILTERED ( 'TableGCS'[slicer GCS] )
                    && MAX ( 'GCS'[Is GCS] ) = seleGCS,
                1,
                IF (
                    ISFILTERED ( 'TableCQR'[slicer CQR] )
                        && MAX ( 'CQR'[Is CQR] ) = seleCQR,
                    1,
                    0
                )
            )
        ),
        CROSSFILTER ( Main[Client], Sponsors[Client], BOTH )
    )

The charts changes, however I have a doubt about what is the result you want to see on the final chart because you are using a max function that returns only the maximum value for each of your values returning a single colour.

Pbix file attach.