Forum Discussion

powerBIpeon's avatar
powerBIpeon
Helper II
7 years ago
Solved

Not Selected Values

similart to this thread, however i want to know if there is a way to have these customers grouped

https://community.powerbi.com/t5/Desktop/Not-Selected-Values/m-p/572530#M270416

 

Table1 has Column CUST and another Table2 Column CUST in groups.

Slicer is PIN to CUST Column in Table 1, if User selects multiple value such as CUST1, CUST5, CUST 8,.....etc. Is there a way to list rest of the not selected values based on the grouping of these customers?

 

here is the link to the file

 

https://www.dropbox.com/s/1fg0pvc3ptkd7tv/Not%20Selected%20Values.pbix?dl=0

 

 

Thanks

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi powerBIpeon

     

    You may try below measure:

    Measure =
CONCATENATEX (
    FILTER (
        Table2,
        Table2[Group] = MAX ( Table2[Group] )
            && NOT ( Table2[Cust] ) IN VALUES ( Table1[Cust] )
    ),
    Table2[Cust],
    ","
)

    Regards,

    Cherie

5 Replies

    • powerBIpeon's avatar
      powerBIpeon
      Helper II
      7 years ago

      Greg_Deckler - the formula does work, but its repeating the the non-selected customer multiple times in the group. I changed the SUM to CONCATENATEX

      InverseSum = IF(
                            ISFILTERED('InverseAggregator'[Category]),
                            CALCULATE(
                                                CONCATENATEX('InverseAggregator'[Value]),
                                                EXCEPT(
                                                             ALL('InverseAggregator'[Category]),
                                                             VALUES('InverseAggregator'[Category])
                                                 )
                            ),
                            CONCATENATEX('InverseAggregator'[Value])
                        )

       

       

      This is what the output is when CUST1 is not selected. 

      Group1 - CUST1 CUST1 CUST1 CUST1 CUST1

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi powerBIpeon

         

        You may try below measure:

        Measure =
CONCATENATEX (
    FILTER (
        Table2,
        Table2[Group] = MAX ( Table2[Group] )
            && NOT ( Table2[Cust] ) IN VALUES ( Table1[Cust] )
    ),
    Table2[Cust],
    ","
)

        Regards,

        Cherie