Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

SUMIFS with multiple columns with DAX

  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 ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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" )

     

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
4 years ago

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"))).