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.
Hello Experts,
Through the following table, I am looking for 3 tables by DAX.
1- A table that shows all users that have 3 certifications A,B,C (complete certification)
2 A table that shows the users and their certifications and missed certifications
3- A table that shows users that have not any certification.
Solved! Go to Solution.
@koorosh
Here are table codes
EVALUATE
FILTER (
VALUES ( Table1[User] ),
COUNTROWS (
INTERSECT (
CALCULATETABLE ( VALUES ( Table1[Certification] ) ),
{ "Cert A", "Cert B", "Cert C" }
)
) = 3
)
EVALUATE
ADDCOLUMNS (
CROSSJOIN (
VALUES ( Table1[user] ),
SELECTCOLUMNS ( { "Cert A", "Cert B", "Cert C" }, "Cert", [Value] )
),
"Status",
VAR _cert = [Cert]
RETURN
IF (
CALCULATE ( ISEMPTY ( FILTER ( table1, table1[Certification] = _cert ) ) ),
"Missing",
"Certified"
)
)
EVALUATE
FILTER (
VALUES ( Table1[User] ),
COUNTROWS (
INTERSECT (
CALCULATETABLE ( VALUES ( Table1[Certification] ) ),
{ "Cert A", "Cert B", "Cert C" }
)
) = 0
)
Check the attached file below my signature
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @koorosh ,
Here the third one:
Table3 = FILTER ( SUMMARIZE ( 'Table', Table[User], "NumberOfDistinctCertifications", CALCULATE ( DISTINCTCOUNT ( Table[Certification] ), Table[Certification] in {"Cert A", "Cert B", "Cert C"} ), "NumberOfDistinctCourses", CALCULATE ( DISTINCTCOUNT ( Table[Certification] ), Table[Certification] in {"course"} ) ), [NumberOfDistinctCourses] > 0 && [NumberOfDistinctCertifications] < 1 )
Does this help? 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
@koorosh
Here are table codes
EVALUATE
FILTER (
VALUES ( Table1[User] ),
COUNTROWS (
INTERSECT (
CALCULATETABLE ( VALUES ( Table1[Certification] ) ),
{ "Cert A", "Cert B", "Cert C" }
)
) = 3
)
EVALUATE
ADDCOLUMNS (
CROSSJOIN (
VALUES ( Table1[user] ),
SELECTCOLUMNS ( { "Cert A", "Cert B", "Cert C" }, "Cert", [Value] )
),
"Status",
VAR _cert = [Cert]
RETURN
IF (
CALCULATE ( ISEMPTY ( FILTER ( table1, table1[Certification] = _cert ) ) ),
"Missing",
"Certified"
)
)
EVALUATE
FILTER (
VALUES ( Table1[User] ),
COUNTROWS (
INTERSECT (
CALCULATETABLE ( VALUES ( Table1[Certification] ) ),
{ "Cert A", "Cert B", "Cert C" }
)
) = 0
)
Check the attached file below my signature
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks Mohammed, If the excel data source has more columns and I want to include them in the T1 and T2, wherein Dax for T1 and T2, I should bring them? For example name of the company of each user and the date of each certification.
@koorosh
Yes, you can do it by using these tables as filters in a CALCULATEDTABLE function.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @koorosh ,
Here the first one:
Table1 =
FILTER ( SUMMARIZE ( FILTER ( 'Table', Table20[Certification] in {"Cert A", "Cert B", "Cert C"} ), Table[User], "NumberOfDistinctCertifications", DISTINCTCOUNT ( Table[Certification] ) ), [NumberOfDistinctCertifications] = 3 )
Does this help? 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |