Exclude whole group if value is found

[Ildoin]

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

[itsme-stephanie]

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.

[v-yuta-msft · Accepted solution]

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]

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.