Need Help Please. I need to COUNT a value with an OR and an AND condition from another table.

[The_Clam]

Hi.  Please help.

I need a COUNT of the IDs in Worksheet A that match the following criteria:

Column A is greater than 25 OR Column B greater than 250

AND

The ID from Worksheet A equals an ID2 from Worksheet B

The COUNT should be 17.

Here is the link for the Power BI file.  Thanks!!!

https://drive.google.com/file/d/14uuEkzotLyugR7BY2IXo3R-b89boXLHM/view?usp=sharing

[v-eqin-msft]

Hi @The_Clam ,

Actually in my test ,the condition1 returns 19 rows not 17 and the condition2 will return 2 rows.

You may firstly refer to @Jihwan_Kim 's method which  works fine.

Or my method is:

count <> = 
var _t= DISTINCT(SUMMARIZE(FILTER('Worksheet_A',[A]>25 || [B]>250),[ID]))
RETURN COUNTROWS(FILTER(_t,( [ID] IN VALUES(Worksheet_B[ID2])) =FALSE()  ))

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

[Ashish_Mathur]

Hi,

Here's what i would do.

Write this calculated column formula in worksheet_A

Available in worksheet_B = LOOKUPVALUE(Worksheet_B[ID2],Worksheet_B[ID2],Worksheet_A[ID])

Write this measure and drag it to a card visual

Measure = calculate(distinctcount(Worksheet_A[ID]),FILTER(Worksheet_A,Worksheet_A[ID]=Worksheet_A[Available in worksheet_B]&&(Worksheet_A[A]>25||Worksheet_A[B]>250)))

The result will be 19.

Hope this helps.

[CNENFRNL]

For fun only, see the power of Excel worksheet formula,

[The_Clam]

@Jihwan_Kim, Yes!  That works.  What if I wanted to do the opposite for the 2nd criteria, so it would be....."AND The ID from Worksheet A does NOT equal an ID2 from Worksheet B".  What is the opposite of IN?  I tried putting NOT IN, but it didn't work.

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Please try the below.

expected result measure: = 
COUNTROWS (
    CALCULATETABLE (
        DISTINCT ( Worksheet_A[ID] ),
        FILTER (
            Worksheet_A,
            OR ( Worksheet_A[A] > 25, Worksheet_A[B] > 250 )
                && NOT( Worksheet_A[ID] IN DISTINCT ( Worksheet_B[ID2] ) )
        )
    )
)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below measure.

expected result measure: =
COUNTROWS (
    CALCULATETABLE (
        DISTINCT ( Worksheet_A[ID] ),
        FILTER (
            Worksheet_A,
            OR ( Worksheet_A[A] > 25, Worksheet_A[B] > 250 )
                && Worksheet_A[ID] IN DISTINCT ( Worksheet_B[ID2] )
        )
    )
)