Forum Discussion

huguestremblay's avatar
huguestremblay
Icon for Helper II rankHelper II
4 years ago

Help needed with the Visualization of Selection vs Others

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.


 

 
 

 

Need Help

12 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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.

  • huguestremblay's avatar
    huguestremblay
    Icon for Helper II rankHelper II
    4 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        huguestremblay
        Icon for Helper II rankHelper II
        4 years ago

        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.

  • huguestremblay's avatar
    huguestremblay
    Icon for Helper II rankHelper II
    4 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
    huguestremblay
    Icon for Helper II rankHelper II
    4 years ago

    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.