Forum Discussion

zhangb94's avatar
zhangb94
Regular Visitor
3 years ago
Solved

Filtering multiple row data with DAX?

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. 

 

RegionUserMethod of PurchaseItems Purchased
ONBobA2
ONBobA2
ONBobA2
ONBobB7

 

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". 
 
RegionUserMethod of PurchaseItems PurchasedColumn
ONJohnA2 
ONJohnB81

 

Feedback is always welcome and am open to learning!
 
Thank you all!
Need Help
Tips and Tricks
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    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.

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

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

    • zhangb94's avatar
      zhangb94
      Regular Visitor
      3 years ago

      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"

       

      RegionUserMethodOfPurchaseItemsPurchasedColumn
      ONBobA20
      ONBobA20
      ONBobA20
      ONBobB71

       

       

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Community Support
    3 years ago

    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.