DAX Formula Help: Returning Values Based on Multiple Conditions Across Two Tables

[InsightSeeker]

Hello everyone,

I need help writing a DAX formula in Power BI that applies specific conditional logic based on fields in two tables: *Table 1* and *Table 2*. My goal is to return different values based on combinations of values in `settle_code`, `supplier_name`, `Lcard`, and `Value_Code`.

My preference would be creating a calculated column in Table 1.

Here are the specific conditions I’m looking to implement:

1. **Return "card" when:**
- `Table 1[settle_code]` is "CREDIT" **and** `Table 2[supplier_name]` is "SPA(AA)Sale" **and** `Table 2[Lcard]` is "Card"
- `Table 1[settle_code]` is "CASH" **and** `Table 2[supplier_name]` is "SPA(AA)Sale" **and** `Table 2[Lcard]` is "Card"
- `Table 1[settle_code]` is "CARD" **and** `Table 2[supplier_name]` is "SPA(AA)Sale" **and** `Table 2[Lcard]` is "Card"

2. **Return "SPA(AA) Sale" when:**
- `Table 1[settle_code]` is "CREDIT" **and** `Table 2[supplier_name]` is "SPA(AA)Sale" **and** `Table 2[Lcard]` is empty (`""`)
- `Table 1[settle_code]` is "CASH" **and** `Table 2[supplier_name]` is "SPA(AA)Sale" **and** `Table 2[Lcard]` is empty
- `Table 1[settle_code]` is "CARD" **and** `Table 2[supplier_name]` is "SPA(AA)Sale" **and** `Table 2[Lcard]` is empty

3. **Return "UCCF" when:**
- `Table 1[settle_code]` is "UCCF" **and** `Table 1[Value_Code]` is one of ("AX", "CA", "TP", "VI") **and** `Table 2[supplier_name]` is "SPA(AA)Sale" **and** `Table 2[Lcard]` is empty

4. **Return "error" when:**
- `Table 1[settle_code]` is "UCCF" **and** `Table 1[Value_Code]` is not one of ("AX", "CA", "TP", "VI") 

5. **Else return "Other" for all other cases.**

I'm not sure how to best structure this in DAX. Any advice on creating this formula would be much appreciated. Thank you!

TABLE 1

settle_code	settle_name	Value_Code	unique_key
CARD	Card	AXPOS	10158086
CARD	Card	CAPOS	10158088
CARD	Card	TPPOS	10158090
CARD	Card	VIPOS	10158092
UCCF	UCCF	VI	10158094
UCCF	UCCF	CA	10158096
UCCF	UCCF	AX	10158098
UCCF	UCCF	TP	10158100
CREDIT	Credit	0000000100	10158102
CREDIT	Credit	0000000100	10158104
CREDIT	Credit	0000000100	10158106
CASH	Cash	0000000100	10158108
CASH	Cash	0000000100	10158110
CASH	Cash	0000000100	10158112

TABLE 2

unique_key	supplier_name	Lcard
10158086	SPA(AA)Sale
10158088	SPA(AA)Sale
10158090	SPA(AA)Sale
10158092	SPA(AA)Sale	Card
10158094	SPA(AA)Sale
10158096	ABC Corp
10158098	SPA(AA)Sale	Card
10158100	SPA(AA)Sale	Card
10158102	SPA(AA)Sale	Card
10158104	ABC Corp
10158106	ABC Corp
10158108	LPA Corp
10158110	LPA Corp
10158112	KLW Corp

[DataNinja777 · Accepted solution]

Hi @InsightSeeker ,

You can use the dax formula like below using lookupvalue to write a calculated column in Table 1 to produce your required output. 

Result = 
VAR SupplierName = LOOKUPVALUE('Table 2'[supplier_name], 'Table 2'[unique_key], 'Table 1'[unique_key])
VAR LcardValue = LOOKUPVALUE('Table 2'[Lcard], 'Table 2'[unique_key], 'Table 1'[unique_key])

RETURN 
    SWITCH(
        TRUE(),
        
        // Condition 1: Return "card"
        (
            'Table 1'[settle_code] IN {"CREDIT", "CASH", "CARD"} &&
            SupplierName = "SPA(AA)Sale" &&
            LcardValue = "Card"
        ), "card",
        
        // Condition 2: Return "SPA(AA) Sale"
        (
            'Table 1'[settle_code] IN {"CREDIT", "CASH", "CARD"} &&
            SupplierName = "SPA(AA)Sale" &&
            (LcardValue = "" || ISBLANK(LcardValue))
        ), "SPA(AA) Sale",
        
        // Condition 3: Return "UCCF"
        (
            'Table 1'[settle_code] = "UCCF" &&
            'Table 1'[Value_Code] IN {"AX", "CA", "TP", "VI"} &&
            SupplierName = "SPA(AA)Sale" &&
            (LcardValue = "" || ISBLANK(LcardValue))
        ), "UCCF",
        
        // Condition 4: Return "error"
        (
            'Table 1'[settle_code] = "UCCF" &&
            NOT('Table 1'[Value_Code] IN {"AX", "CA", "TP", "VI"})
        ), "error",
        
        // Default case: Return "Other"
        "Other"
    )

I have attached an example pbix file. 

Best regards,

[Bibiano_Geraldo]

Hi @InsightSeeker ,

Please in Table 1 create calculated column and past the following DAX code:

Result = 
SWITCH(
    TRUE(),
    // Condition 1: Return "card"
    
        Table1[settle_code] IN {"CREDIT", "CASH", "CARD"} &&
        LOOKUPVALUE(Table2[supplier_name], Table2[unique_key], Table1[unique_key]) = "SPA(AA)Sale" &&
        LOOKUPVALUE(Table2[Lcard], Table2[unique_key], Table1[unique_key]) = "Card"
    , "card",
    
    // Condition 2: Return "SPA(AA) Sale"

        Table1[settle_code] IN {"CREDIT", "CASH", "CARD"} &&
        LOOKUPVALUE(Table2[supplier_name], Table2[unique_key], Table1[unique_key]) = "SPA(AA)Sale" &&
        ISBLANK(LOOKUPVALUE(Table2[Lcard], Table2[unique_key], Table1[unique_key]))
    , "SPA(AA) Sale",
    
    // Condition 3: Return "UCCF"

        Table1[settle_code] = "UCCF" &&
        Table1[Value_Code] IN {"AX", "CA", "TP", "VI"} &&
        LOOKUPVALUE(Table2[supplier_name], Table2[unique_key], Table1[unique_key]) = "SPA(AA)Sale" &&
        ISBLANK(LOOKUPVALUE(Table2[Lcard], Table2[unique_key], Table1[unique_key]))
    , "UCCF",
    
    // Condition 4: Return "error"
    AND(
        Table1[settle_code] = "UCCF",
        NOT Table1[Value_Code] IN {"AX", "CA", "TP", "VI"}
    ), "error",
    
    // Default: Return "Other"
    "Other"
)

Your table1 should now look like this:

To learn more about SWITCH function, please follow the link: SWITCH 

If this help you, please give a kudo and mark as solution

[shafiz_p]

Hi @InsightSeeker  Try this below code (Using Lookupvalue for unrelated tables):

NewColumn = 
VAR SettleCode = 'Table1'[settle_code]
VAR SupplierName = LOOKUPVALUE('Table2'[supplier_name], 'Table2'[unique_key], 'Table1'[unique_key])
VAR Lcard = LOOKUPVALUE('Table2'[Lcard], 'Table2'[unique_key], 'Table1'[unique_key])
VAR ValueCode = 'Table1'[Value_Code]

RETURN
SWITCH(
    TRUE(),
    (SettleCode IN {"CREDIT", "CASH", "CARD"} && SupplierName = "SPA(AA)Sale" && Lcard = "Card"), "card",
    (SettleCode IN {"CREDIT", "CASH", "CARD"} && SupplierName = "SPA(AA)Sale" && Lcard = ""), "SPA(AA) Sale",
    (SettleCode = "UCCF" && ValueCode IN {"AX", "CA", "TP", "VI"} && SupplierName = "SPA(AA)Sale" && Lcard = ""), "UCCF",
    (SettleCode = "UCCF" && NOT(ValueCode IN {"AX", "CA", "TP", "VI"})), "error",
    "Other"
)

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz