Lookup between two tables

[rpattan]

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.

[v-jiascu-msft]

Hi @rpattan,

Did it work?

Best Regards,

Dale

[rpattan]

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 · Accepted solution]

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

[dattatrayvanti]

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

[rpattan]

Thank You @v-jiascu-msft

It works. Thanks again for your time and patience.

Regards

RK

[v-jiascu-msft]

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],
        "-"
    )

Best Regards,

Dale