Forum Discussion

Ildoin's avatar
Ildoin
New Member
8 years ago
Solved

Exclude whole group if value is found

I am trying to calculate in Power BI values (Sum) for the Items that have only user specified value in column Code. In my case the the whole row should be excluded if the value is other than "X". For example Item AA should be excluded because it has also value "Z".

 

Item	Code	Amount
AA	X	1
AA	X	2
AA	Z	3
BB	X	4
BB	Y	5
BB	Z	6
BB	X	7
CC	X	8
CC	X	9

Table1

 Any ideas how to do this? The end result should be like this:

 

Item	Code	Sum
CC	X	17

This is as far I have gotten:

SumByItem = CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Item]))

 

 

The rows are summed by Item but I am strugling with the exclusion part. Appreciate any help! Thanks

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi Ildoin,

     

    To achieve your requirement, you can create a calculate column like below:

    IsValid Column = 
IF (
    CALCULATE (
        COUNTROWS(Table1),
        ALLEXCEPT ( Table1, Table1[Item] )
    )
        <> CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Item], Table1[Code])),
    FALSE(),
    IF (
        CALCULATE (
            VALUES ( Table1[Code] ),
            ALLEXCEPT ( Table1, Table1[Item], Table1[Code] )
        )
            <> "X",
        FALSE(),
        TRUE ()
    )
)

     

    Then create a slicer based on IsValid Column, select True, you will achieve what you want.

     

     

    Hope it's helpful to you.

     

    Jimmy Tao

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Ildoin,

     

    To achieve your requirement, you can create a calculate column like below:

    IsValid Column = 
IF (
    CALCULATE (
        COUNTROWS(Table1),
        ALLEXCEPT ( Table1, Table1[Item] )
    )
        <> CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Item], Table1[Code])),
    FALSE(),
    IF (
        CALCULATE (
            VALUES ( Table1[Code] ),
            ALLEXCEPT ( Table1, Table1[Item], Table1[Code] )
        )
            <> "X",
        FALSE(),
        TRUE ()
    )
)

     

    Then create a slicer based on IsValid Column, select True, you will achieve what you want.

     

     

    Hope it's helpful to you.

     

    Jimmy Tao

  • Gazzer's avatar
    Gazzer
    Icon for Resolver II rankResolver II
    8 years ago

    I don't know how to write the DAX for this (someone cleverer will come along soon, I'm sure) but I did this using built-in Power BI functions.

     

    I duplicated your table and then filtered it to KEEP rows where the Code - 'Z'

    Then I merged the first table with the new, filtered duplicate, using an 'anti' join type.

     

     

    Lastly, I used Group By on the Item column and aggregated the Amount to get the desired outcome.

     

     

  • itsme-stephanie's avatar
    itsme-stephanie
    Frequent Visitor
    5 years ago

    I'm facing almost the same problem, except that I want to exclude the whole group if the value X is found.

    So if we would extend the intital example:

    Item	Code	Amount
AA	X	1
AA	X	2
AA	Z	3
BB	X	4
BB	Y	5
BB	Z	6
BB	X	7
CC	X	8
CC	X	9
    DD      Y       1
    DD      Z       2
    EE      Y       3
    EE      Z       4
    EE      Z       5

    Table1

     

    My ask would be how to get only a "true" for DD & EE:

     

    Item	IsValidColumn	
AA      False
    BB      False
    CC      False
    DD	True
    EE      True

     

    I've tried to adapt the calculated column example, but it didn't worked out.

    Any hint appriciated.