Forum Discussion

rpattan's avatar
rpattan
Advocate I
8 years ago
Solved

Lookup between two tables

  Hi,   Good Day..!   I have two tables named 1. Certificates 2. Delta. The Certificates table consists of employees who hold the certificates, and the Delta table consists of those who are quar...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi rpattan,

     

    I hope your data isn't sensitive. Please be aware of your privacy.

    Try this formula please. I didn't get the exact number you wrote. But I checked the answer.

    Please also check out the demo in the attachment.

    Measure 2 =
CALCULATE (
    COUNTROWS ( Certifications ),
    FILTER (
        Certifications,
        NOT Certifications[UserID & Certification] IN VALUES ( Deltas[UserID & Delta Name] )
    )
)

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi rpattan,

 

Please check out the demo in the attachment. The measure could be the one below.

Measure =
VAR planQuarter =
    VALUES ( Delta[Quarter Obtained] )
VAR attended =
    CALCULATETABLE (
        VALUES ( Delta[Quarter Obtained] ),
        FILTER (
            Delta,
            [User Sys ID] = MIN ( 'Certificates'[EMP User ID] )
                && [Delat Name] = MIN ( 'Certificates'[Cert] )
        )
    )
RETURN
    CONCATENATEX (
        FILTER ( planQuarter, NOT [Quarter Obtained] IN attended ),
        [Quarter Obtained],
        "-"
    )
Lookup_between_two_tables

Best Regards,

Dale