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('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???
Need Help

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Farhan75

     

    Try this one

     

    Measure =
CONCATENATEX (
    EXCEPT ( ALL ( Table1[Column1] ), ALLSELECTED ( Table1[Column1] ) ),
    Table1[Column1],
    ", "
)
    • 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,

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, 

     

    I am doing something similar to this, the difference is that I am trying to calculate an average(frmo a different table) for all of the values not selected. This sis the calculation I am using

    Trend Chart Other =
    IF (
    ISBLANK ( [Avg Result] ),
    BLANK (),
    CALCULATE (
    [Avg Result],
    EXCEPT (
    ALL ( SurveyQuestionQ11[value], SurveyQuestionQ11[ResponseId] ),
    ALLSELECTED ( SurveyQuestionQ11[value],SurveyQuestionQ11[ResponseId])
    )
    )
    )
     
    Avg Result is a measure from another table but has a relationship to SurveyQuestionQ11. 
    The math is not calcualting correctly and I cannot figure out why.