Power BI multiple conditions for countifs

1
votes

I am trying to achieve the countifs here for the following combination item and code.

Column A&B has Item and code and column C my status.

1.if same item has "YP" after the code and there is no "YP" after the code then my status is "NotOKay".

Example;

enter image description here

2.There is no "YP" after the code for the same item then my status is "Okay".

enter image description here

enter image description here

enter image description here

1
powerbidaxcountif
Is there always will be a dash "-" before the string YP? – mkRabbani
yes. There is always dash "-" before starting the YP. – PETER
What are you trying to count? OK rows? Distinct OK items? – Alexis Olson
I am new to Power Bi. In Excel I am trying to make a formula countifs+ find or search. – PETER

1 Answers

1
votes

You can try this below code applicable for both measure and column-

ok_not_ok = 

VAR item_count = 
CALCULATE(
    COUNT(your_table_2[item]),
    ALLEXCEPT(your_table_2,your_table_2[item])
)

VAR item_count_with_yp =
CALCULATE(
    COUNT(your_table_2[item]),
    FILTER(
        ALLEXCEPT(your_table_2,your_table_2[item]),
        RIGHT(your_table_2[code],3) = "-YP"            
    )
) + 0

RETURN IF(
    item_count_with_yp = 0 || item_count=item_count_with_yp, 
    "OK", 
    "NOT OK"
)

Here is sample output-

enter image description here