cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Farhan75 Frequent Visitor
Frequent Visitor

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('Table1,'Table1'[Column1]),
ALLSELECTED('Table1'[Column1])
)
,'Table1'[Column1],", "
)

 

Error = "Each table argument of 'EXCEPT' must have the same number of columns."

 

To get around this, I tried wrapping a SELECTCOLUMNS function around ALLEXCEPT, but I'm getting a different error

 

CONCATENATEX(
EXCEPT(
SELECTCOLUMNS(ALLEXCEPT('Table1,'Table1'[Column1]),"Column1",'Table1'[Column1]),
ALLSELECTED('Table1'[Column1])
)
,'Table1'[Column1],", "
)
 
Error = "A single value for column "Column1" in table 'Table1' cannot be determined"
 
Hoping somebody can help me out with this please???
5 REPLIES 5
Super User III
Super User III

Re: Using EXCEPT with ALLEXCEPT and ALLSELECTED

@Farhan75

 

Try this one

 

Measure =
CONCATENATEX (
    EXCEPT ( ALL ( Table1[Column1] ), ALLSELECTED ( Table1[Column1] ) ),
    Table1[Column1],
    ", "
)
Try my new Power BI game Cross the River
Super User III
Super User III

Re: Using EXCEPT with ALLEXCEPT and ALLSELECTED

@Farhan75

 

Your measure could also work if you make these modifications Smiley Wink

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],", ")
Try my new Power BI game Cross the River
Community Support Team
Community Support Team

Re: Using EXCEPT with ALLEXCEPT and ALLSELECTED

Hi @Farhan75,

 

By my tests, the solutions from Zubair_Muhammad are all helpful. 

 

If you have solved you problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Farhan75 Frequent Visitor
Frequent Visitor

Re: Using EXCEPT with ALLEXCEPT and ALLSELECTED

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

Community Support Team
Community Support Team

Re: Using EXCEPT with ALLEXCEPT and ALLSELECTED

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.

 

 

result.PNG

 

Here is the attachment you could refer to.

 

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

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors