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,

 

Did it work?

 

Best Regards,

Dale

rpattan's avatar
rpattan
Advocate I
8 years ago

Hi v-jiascu-msft,

 

Thank you for the followup. I was actually on vacation. My sincere thanks for your time and workout.

 

Your solution quite close to the expected answer. This query is quite a little complex in understanding, here I am attaching the PBIX file with actual data. Therefore you could understand in detail.

 

Request to open the pbix.file attached. Explanation given in the file.

 

PBIX File: 

https://drive.google.com/file/d/1vmHKwf_3E9u4xmi38vzu44s2mLqVutxQ/view?usp=sharing

 

Excel File:

https://drive.google.com/file/d/1d1zFZOzUgBXtOmDWJCn-tSgt8TxiU20C/view?usp=sharing

 

Thank you for your time. Appreciate.

 

Regards

RK.

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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

    • rpattan's avatar
      rpattan
      Advocate I
      8 years ago

       

      Thank You v-jiascu-msft

       

      It works. Thanks again for your time and patience.

       

      Regards

      RK

    • dattatrayvanti's avatar
      dattatrayvanti
      Regular Visitor
      3 years ago

      Dear Team

       

      I having Similar issue in different Way

      I am Having Unique data set Where i wantd to map the sales data ( But Sales data in in Day format..Which is showin in above Issue)

       

      Requesting you please suggues me how to map day wise sales to Unique consumer Data