Forum Discussion
Need Dax for below logic
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
)
)