SUMIFS with multiple columns with DAX

[Saxon10]

Hi,

How can I do sumifs in Power BI based on the below mentioned data.

In Excel I am apply the following formula =IF(OR($C3="Not Pair",SUMIFS(D$3:D$73,A$3:A$73,A3,C$3:C$73,C3)=1),$B3,"Not Okay") to achieve my final output.

I am looking for new calculated column option.

Item	Colour Code	Colour Code	Unique Count	Desired Result
123	Orange	A1	1	Orange
124	Green	A1	1	Green
125	Orange	A2	1	Orange
126	Green	A2	1	Green
127	Orange	Not Pair	1	Orange
128	Orange	Not Pair	1	Orange
135	Orange	A1	1	Orange
135	Green	A2	1	Green
136	Orange	A2	1	Not Okay
136	Green	A2	1	Not Okay
137	Orange	A1	1	Orange
137	Orange	Not Pair	1	Orange
137	Orange	Not Pair	1	Orange
138	Green	A1	1	Green
138	Orange	Not Pair	1	Orange
138	Orange	Not Pair	1	Orange
139	Orange	A1	1	Orange
139	Orange	Not Pair	1	Orange
140	Green	A1	1	Green
140	Orange	Not Pair	1	Orange
141	Orange	A2	1	Orange
141	Orange	Not Pair	1	Orange
141	Orange	Not Pair	1	Orange
142	Green	A2	1	Green
142	Orange	Not Pair	1	Orange
142	Orange	Not Pair	1	Orange
143	Green	A1	1	Green
143	Green	A2	1	Green
143	Orange	Not Pair	1	Orange
144	Orange	A1	1	Not Okay
144	Green	A1	1	Not Okay
144	Orange	Not Pair	1	Orange
145	Orange	A1	1	Not Okay
145	Green	A1	1	Not Okay
145	Orange	A2	1	Not Okay
145	Green	A2	1	Not Okay
147	Green	A2	0.5	Green
147	Green	A2	0.5	Green
148	Orange	A1	0.5	Orange
148	Orange	A1	0.5	Orange
149	Green	A1	0.5	Green
149	Green	A1	0.5	Green
150	Orange	A1	1	Orange
150	Orange	A2	1	Orange
151	Green	A1	1	Green
151	Green	A2	1	Green
152	Orange	A1	1	Orange
152	Orange	Not Pair	1	Orange
152	Orange	A2	1	Orange
153	Orange	A1	1	Not Okay
153	Green	A1	1	Not Okay
153	Orange	A2	1	Orange
1999	Orange	A2	1	Not Okay
1999	Green	A2	1	Not Okay
1999	Orange	A1	1	Orange
155	Green	A1	0.5	Green
155	Green	A2	1	Not Okay
156	Orange	A2	0.5	Orange
156	Green	A1	0.5	Green
157	Orange	Not Pair	1	Orange
157	Orange	Not Pair	1	Orange
154	Orange	A1	1	Not Okay
154	Orange	A2	1	Orange
154	Green	A1	1	Not Okay
155	Orange	A2	1	Not Okay
155	Green	A1	0.5	Green
155	Orange	Not Pair	1	Orange
156	Orange	A2	0.5	Orange
156	Green	A1	0.5	Green
156	Orange	Not Pair	1	Orange
156	Orange	Not Pair	1	Orange

[AlexisOlson · Accepted solution]

You can't have two columns with the same name, so I change the first to "Colour" and second to "Code":

Result =
VAR SumIfs =
    CALCULATE (
        SUM ( Table1[Unique Count] ),
        ALLEXCEPT ( Table1, Table1[Item], Table1[Code] )
    )
RETURN
    IF ( Table1[Code] = "Not Pair" || SumIfs = 1, Table1[Colour], "Not Okay" )

[AlexisOlson · Accepted solution]

You can't have two columns with the same name, so I change the first to "Colour" and second to "Code":

Result =
VAR SumIfs =
    CALCULATE (
        SUM ( Table1[Unique Count] ),
        ALLEXCEPT ( Table1, Table1[Item], Table1[Code] )
    )
RETURN
    IF ( Table1[Code] = "Not Pair" || SumIfs = 1, Table1[Colour], "Not Okay" )

[Saxon10]

@AlexisOlson ,

Thanks for your respones and sorry for the late reply.
Your solution almost working fine but I recevied incorrect result were the same itms has repeat multiple times

[v-kkf-msft · Accepted solution]

Hi @Saxon10 ，

Please add the ROUND function to the formula of AlexisOlson.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

@Saxon10 ROUND is a good modification but rounding to zero decimal places might be a bit too round. If you run into trouble, try ROUND ( SumIfs, 1 ) instead.

[Saxon10]

@AlexisOlson ,

Thanks for your tips and advise. I will test both scenario at my end.

[Saxon10]

Thanks for your reply and your solution. 

Your solution working well. Thank you.

[AlexisOlson]

I'm guessing you have some kind of rounding error so that Unique Count isn't adding up to exactly 1.

I don't understand why you have duplicated rows, so it's hard to suggest a fix.

[amitchandak]

@Saxon10 , Try a new column like

new col =
var _1 = countx(filter(Table, [Item] && earlier([Item]) && [Colour Code] <> "Not Pair"), [Item])
return
if([Colour Code] = "Not Pair" || isblank(_1) || _1=1, [Colour], "Not Okay")

[Saxon10]

I am receeving error if I apply your code "The syntax for 'Table' is incorrect. (DAX(var _1 = countx(filter(Table, [Item] && earlier([Item]) && [Colour Code] <> "Not Pair"), [Item])returnif([Colour Code] = "Not Pair" || isblank(_1) || _1=1, [Colour Code], "Not Okay"))).

[Saxon10]

thanks for your reply.

I am receving the error can't convert value "item column" of type text to type true /false.

Could you please share your PBI.