If the value is N/A then value getting from another table else same tbl but no relationships b/w tbl

[basirve]

Dear Experts,

How to achive below case stament in DAX and no Direct relationships bewteen Table A  , Table B.

[v-eqin-msft]

Hello @basirve ,

In my opinion, you want to get values based on the conditions in another table when the two tables have no relationship, right?

You can use LOOKUPVALUE() as shown below after adding the column from the other table:

B.Emp.ID =
CALCULATE ( ALLSELECTED ( 'B'[Emp.ID] ), FILTER ( 'B', 'B'[ID] = 'A'[ID] ) )

Column =
VAR _COL =
    LOOKUPVALUE ( 'A'[B.Emp.ID], 'A'[ID], [ID] )
RETURN
    IF ( [Emp.ID] IN { "NOT APPLICABLE", "UNKNOWN" }, _COL, [Emp.ID] )

O

Measure =
VAR _c =
    LOOKUPVALUE ( 'A'[B.Emp.ID], 'A'[ID], MAX ( 'A'[ID] ) )
RETURN
    IF (
        MAX ( 'A'[Emp.ID] ) IN { "NOT APPLICABLE", "UNKNOWN" },
        _c,
        MAX ( 'A'[Emp.ID] )
    )

My visualization looks like this:

Here's the pbix file.

Have I answered your question? Please mark my answer as a solution. Thanks a lot.
If not, load some insensitive data samples and expected output.

Best regards
Eyelyn Qin

[smpa01 · Accepted solution]

@basirve I guess you're thinking of a calculated column.

If you have two tables below

T1

Columna1	Value
A	100
B	200
C	Not applicable
D	Not applicable

T3

Columna1	Value
C	300
D	400

You can then achieve the equivalent SQL CASE result by creating a column calculated in T1 and usin LOOKUPVALUE/TREATAS/INTERSECT/CONTAINS. None of them have any relationship dependencies. Ensure that thet Value in bith tables have the same data type.

LOOKUPVALUE = 
IF (
    [Value] <> "Not Applicable",
    [Value],
    LOOKUPVALUE ( T3[Value], T3[Column1], T1[Column1] )
)

TREATAS = 
IF (
    [Value] <> "Not Applicable",
    [Value],
    CALCULATE (
        CALCULATE (
            MAXX ( T3, T3[Value] ),
            TREATAS ( VALUES ( T1[Column1] ), T3[Column1] )
        )
    )
)

INTERSECT = 
IF (
    [Value] <> "Not Applicable",
    [Value],
    CALCULATE (
        CALCULATE (
            MAXX ( T3, T3[Value] ),
            INTERSECT ( ALL ( T3[Column1] ), VALUES ( T1[Column1] ) )
        )
    )
)

CONTAINS = 
IF (
    [Value] <> "Not Applicable",
    [Value],
    CALCULATE (
        CALCULATE (
            MAXX ( T3, T3[Value] ),
            FILTER (
                ALL ( T3[Column1] ),
                CONTAINS ( VALUES ( T1[Column1] ), T1[Column1], T3[Column1] )
            )
        )
    )
)

The PBIX is connected.

[smpa01]

@basirve did you try the solution provided here ?

[Fowmy]

@basirve 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[basirve]

[basirve]

below logic is not fiting due to no relationship

IF (Table1[Emp.ID] IN {"NOT APPLICABLE","UNKNOWN"},Table2[Emp.ID],Table2[Emp.ID])