Need Dax for below logic

[RadhakrishnaE]

Hi All,

I have a requireement like

If table1.columnA = table2.columnB then i want to print Table1.columnc

Thanks in advance

[Fsciencetech]

Try Below DAX  By creating NewTable

1.   ADDCOLUMNS (
    Lookup1,
    "CheckPerson",
        VAR Person = Lookup1[Commission Person]
        VAR _Id = Lookup1[Pid]
        RETURN
            LOOKUPVALUE (
                Lookup2[Commission Person],
                Lookup2[Commission Person], Person,
                Lookup2[     Polid  ], _Id
            )
)

2.

GENERATE(
    Lookup1,
    VAR Person = Lookup1[Commission Person]
    VAR _Id = Lookup1[Pid]
    RETURN
        CALCULATETABLE (
            SELECTCOLUMNS ( Lookup2, "CheckPerson", Lookup2[Commission Person] ),
            Lookup2[Commission Person] = Person,
            Lookup2[     Polid  ]= _Id
        )
)

3.

GENERATE (
    Lookup1,
    VAR Person = Lookup1[Commission Person]
    VAR _Id = Lookup1[Pid]
    RETURN
        CALCULATETABLE (
            SELECTCOLUMNS ( Lookup2, "CheckPerson", Lookup2[Commission Person] ),
            TREATAS ( { Person }, Lookup2[Commission Person] ),
            TREATAS ( { _Id }, Lookup2[     Polid  ] )
        )
)

4. 

VAR FirstTable =
    SELECTCOLUMNS (
        Lookup1,
        "InId", Lookup1[InId],
        "Commission Person", Lookup1[Commission Person] & "",
        "Pid", Lookup1[Pid] & "",
        "Amount", Lookup1[Commission amount]
    )
VAR SecondTable =
    SELECTCOLUMNS (
        Lookup2,
        "Pid", TRIM ( Lookup2[     Polid  ] & "" ),
        "Commission Person", Lookup2[Commission Person] & "",
        "CheckPerson", Lookup2[Commission Person]
    )
RETURN
    NATURALLEFTOUTERJOIN ( FirstTable, SecondTable )

5.

GENERATE (
    Lookup1,
    VAR Person = Lookup1[Commission Person]
    VAR _Id = Lookup1[Pid]
    RETURN
        CALCULATETABLE (
            ROW ( "CheckPerson", VALUES ( Lookup2[Commission Person] ) ),
            Lookup2[Commission Person] = Person,
            Lookup2[     Polid  ] = _Id
        )
)

[Fsciencetech]

 What should be the output table?

[RadhakrishnaE]

Expected Output

InNo	Commission person	Commission Amount	Pid
In1	Agency	10	P1
In2	Agency	40	P2
In3	broker	50	P3

[Fsciencetech]

Please send sample data to understand your requirement.

[RadhakrishnaE]

Table 1

InId	Pid	Commission Person	Commission amount
In1	P1	Agency	10
In2	P1	broker	20
In3	P2	Executive	30
In4	P2	Agency	40
In5	P3	Broker	50

Table2

PolTid	Polid	Commission Person
PolT1	P1	Agency
PolT2	P1	Broker
PolT3	P1	Executive
PolT4	P2	Agency
PolT5	P3	Agency
PolT6	P3	Broker
PolT7	P3	Executive

Condition is if Table1.commission person = table2.commission person then i want to print Table1.Commission amount

for that i am trying lookupvalue(table1.Commission amount,table1.commission person,table2.commission person)

but getting failed, i want to apply condition on commission person only

Thanks in Advance.

[ERD]

Hi @RadhakrishnaE ,

you can use the measure below:

YourMeasure =
VAR c_polid = SELECTEDVALUE ( 't1-2'[Polid] )
VAR c_commperson = SELECTEDVALUE ( 't1-2'[Commission Person] )
RETURN
    CALCULATE (
        MAX ( 't1-1'[Commission amount] ),
        't1-1'[Commission Person] = c_commperson,
        't1-1'[Pid] = c_polid
    )

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