Filtering multiple row data with DAX?

[zhangb94]

Hello Experts!

I hope you're able to help me out here... I am relatively new to Power BI and DAX but will try my best to explain.

I am trying to determine the amount of Users either by a calculcated column via DAX with the following logic or through another method (am open to feedback and suggestions):

• If user purchased any amount of items (items purchased) via Method B, and has atleast one item purchased via Method A, this should count as "0".
• If user purchased 7 or more items (items purchased) via Method B ONLY, this counts as 1 user. 

Region	User	Method of Purchase	Items Purchased
ON	Bob	A	2
ON	Bob	A	2
ON	Bob	A	2
ON	Bob	B	7

I've created a table via DAX using SUMMARIZE, and created a calculcated column using DAX to formulate:

COLUMN =
var _value =
IF (AND(
TableName[MethodOfPurchase] = "A", TableName[ItemsPurchased] > 7), 0,
IF (AND(TableName[MethodOfPurchase] = "B", TableName[ItemsPurchased] >=7), 1, BLANK())
)
RETURN _value

which results in "1" when it should be "0". 

Region	User	Method of Purchase	Items Purchased	Column
ON	John	A	2
ON	John	B	8	1

Feedback is always welcome and am open to learning!

Thank you all!

[v-xiaosun-msft · Accepted solution]

Hi @zhangb94 ,

According to your description, here is my solution.

Since the sample data you gave does not match the expected output you wanted, I create a new sample based on your description.

Create a column.

Column =
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[User] = EARLIER ( 'Table'[User] )
                && [Method of Purchase] = "A"
        )
    ) > 0
        && COUNTROWS (
            FILTER (
                'Table',
                'Table'[User] = EARLIER ( 'Table'[User] )
                    && [Method of Purchase] = "B"
            )
        ) > 0,
    0,
    IF (
        COUNTROWS (
            FILTER (
                'Table',
                'Table'[User] = EARLIER ( 'Table'[User] )
                    && [Method of Purchase] = "B"
                    && [Items Purchased] >= 7
            )
        ) > 0,
        1
    )
)

Final output:

Best Regards,
Community Support Team _ xiaosun

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

[lbendlin]

your sample data does not match the expected output. Please check.

[zhangb94]

Thanks for your response. Updated DAX:

COLUMN =
var _value =
IF (AND(
TableName[MethodOfPurchase] = "A", TableName[ItemsPurchased] < 7), 0,
IF (AND(TableName[MethodOfPurchase] = "B", TableName[ItemsPurchased] >=7), 1, BLANK())
)
RETURN _value

SHOULD yield the results in "COLUMN"

Region	User	MethodOfPurchase	ItemsPurchased	Column
ON	Bob	A	2	0
ON	Bob	A	2	0
ON	Bob	A	2	0
ON	Bob	B	7	1