Forum Discussion

RadhakrishnaE's avatar
RadhakrishnaE
Frequent Visitor
4 years ago

Need Dax for below logic

Hi All,

I have a requireement like

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

 

Thanks in advance

 

 

Need Help

6 Replies

    • RadhakrishnaE's avatar
      RadhakrishnaE
      Frequent Visitor
      4 years ago

      Table 1

      InIdPidCommission PersonCommission amount
      In1P1Agency10
      In2P1broker20
      In3P2Executive30
      In4P2Agency40
      In5P3Broker50

       

      Table2

      PolTid     Polid  Commission Person
      PolT1      P1Agency
      PolT2    P1Broker
      PolT3    P1Executive
      PolT4    P2Agency
      PolT5    P3Agency
      PolT6    P3Broker
      PolT7    P3Executive

       

      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's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.

    • RadhakrishnaE's avatar
      RadhakrishnaE
      Frequent Visitor
      4 years ago

      Expected Output

      InNoCommission personCommission AmountPid
      In1Agency10P1
      In2Agency40P2
      In3broker50P3

       

  • Fsciencetech's avatar
    Fsciencetech
    Icon for Helper III rankHelper III
    4 years ago

    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
            )
    )