Forum Discussion

Farhan75's avatar
Farhan75
Frequent Visitor
7 years ago

Using EXCEPT with ALLEXCEPT and ALLSELECTED

Hi   I'm trying to use EXCEPT with ALLEXCEPT and ALLSELECTED to get the list of values in Column1 have been removed by a filter (i.e. the values not selected)   CONCATENATEX( EXCEPT( ALLEXCEPT(...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Farhan75

 

Your measure could also work if you make these modifications :smileywink:

1) In ALLEXCEPT's arguments put all columns other than column1 (in red font below)
2) Instead of Table1[Column1] use only [Column1] because SelectColumns removes data lineage (in red font below)

 

Measure = CONCATENATEX(
EXCEPT(
SELECTCOLUMNS(ALLEXCEPT(Table1,Table1[OtherColumn2],Table1[OtherColumn3]),"Column1",Table1[Column1]),
ALLSELECTED(Table1[Column1])
),Table[Column1],", ")
Farhan75's avatar
Farhan75
Frequent Visitor
7 years ago

Hi Zubair_Muhammad

 

Thanks for your reply.

 

The reason I am using ALLEXCEPT with Column1 is because I only want to display the values excluded by direct filtering on this column, not by cross filtering on other columns. 

 

I'd already tried your first example, but unfortuantely, this also gives me values excluded by cross filtering. So does your second. Please see example PBIX below.

 

PBIX Example

 

Many thanks for your help so far

 

Kind regards

 

Farhan

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Farhan75,

     

    Sorry for the delay.

     

    Based on your sample pbix, I found that the problem is caused by the slicer of column1 will be filered by the slicers of column 2 and column 3. 

     

    By my tests, I have no better dax ecpression to display the value which will not be filtered by column 1, column2 and column3 at the same time.

     

    As a workaround, you could try this measure below and turn off the interaction between the column 2 and column 3 for that measure.

     

     

    MyMeasure = --show values DIRECTLY unselected excluding those removed by cross filtering
IF(
    ISFILTERED('Table1'[Column1]), -- checks if filtered
        CONCATENATEX(
            EXCEPT(
                ALL(Table1[Column1]),
                ALLSELECTED(Table1[Column1])
            )
            ,[Column1],", "
        )
    ,"All"
)

    Here is your desired output.

     

     

     

    Here is the attachment you could refer to.

     

    If I get the better solution, I will update here.

     

    Best Regards,

    Cherry