cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
The_Clam
Helper I
Helper I

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

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

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Eyelyn9
Community Support
Community Support

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

Eyelyn9_0-1653963027761.png

 

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
CNENFRNL
Super User
Super User

CNENFRNL_0-1653597075590.png

 

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

CNENFRNL_1-1653597222788.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

The_Clam
Helper I
Helper I

@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.

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors