Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
*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;
Once the data mathed then I'm applying the below DAX for counting.
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.
Solved! Go to 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
Hi @rpattan,
Did it work?
Best Regards,
Dale
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.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |