Forum Discussion

lakshmi9228's avatar
lakshmi9228
Icon for Helper III rankHelper III
8 years ago
Solved

how do I create a column taking columns from different tables using if condition

I have a quantity column from one table, 
a dimension column from other table which is grouped by 2 - Bus , other,


Need to create a new column if naming 
if quantity > 2, business
if quantity <=2 && group = bus , business
other

Please help me with the solution.
Thanks

Need Help
  • edhans's avatar
    edhans
    8 years ago

    I'm not completely clear on what you are asking.  See if this file does what you want. 

    I created a calculated column that I think has the logic you are looking for.

     

    Category = 
VAR TotalQuantity = CALCULATE(SUM('Fact Table'[Quantity]))
RETURN
IF(
    TotalQuantity > 2,
    "Business",
    IF(
        TotalQuantity <= 2 && RELATED('Dimension Table'[Group]) = "Business",
        "Business",
        "Other"
    )
)

    If that isn't what you need, please provide some sample data - both tables.

2 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I'm not completely clear on what you are asking.  See if this file does what you want. 

    I created a calculated column that I think has the logic you are looking for.

     

    Category = 
VAR TotalQuantity = CALCULATE(SUM('Fact Table'[Quantity]))
RETURN
IF(
    TotalQuantity > 2,
    "Business",
    IF(
        TotalQuantity <= 2 && RELATED('Dimension Table'[Group]) = "Business",
        "Business",
        "Other"
    )
)

    If that isn't what you need, please provide some sample data - both tables.

    • lakshmi9228's avatar
      lakshmi9228
      Icon for Helper III rankHelper III
      8 years ago

      Hi edhans,

      I was trying the same thing before - due to small syntax problem.
      It was not working.

      Got where the mistake is from.

      Thank you