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 quarter wise attending the interval exams for such certifications in order to keep them unexpired.

 

I'm looking for those users who are missed to attend the interval exams on quarter basis.

 

Note: All certifications are having certified dates. But those dates are not suppose to consider.

 

 Certificates Data:                                    Delta Data:

 Certificates Data           Delta Data

*Relationship between the two tables may not possible since there is no unique strings. 

The output shall be UserID, Certificates columns from Certificates table and Missing Quarter as selected from date dimension table. Like Below; 

 

I've created calculated column as below;

 

Spoiler
Missing Delta =
CALCULATE (
COUNTROWS( Deltas ),
FILTER ( ALL ( Deltas ), Certifications[Certificates] = Deltas[Delta Name] )
)

Once the data mathed then I'm applying the below DAX for counting.

Spoiler
Missing Delta Count =
CALCULATE ( COUNTROWS ( Certifications), Certifications[Missing Delta] = BLANK () )

I'm getting the missing users data, but it is comparing with delta table dates, but I'm expecting that the data should compare with only expected quarter (Q1 2018) from Delta table. Because an user could have attended the delta course in 2017/Q4 and may not attend in 2018/Q1. 

 

Please let me know for more understanding the problem.

 

Thanks

RK.

 

 

 

 

 

 

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

6 Replies

  • 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