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 i would really appreciate some help with my problem below.
I have a fact table that contains certificates (with unique certificate number) and their expiry dates. These can be renewed at a given time frame around the expiry date (eg. -/+ 3 months) ory may just expire on the expiry date (with no "renewal window"). I get this data ready from a sharepoint list.
Some of these certificates are subject to surveys. So there is another fact table with the surveys and the two are releated with an ID column. Not all certificates have surveys and also the surveys may have the same logic as to their due date (Survey window).
I want to present the certificate due dates (start window/expiry date/end window) in matrix visual (or other) and under them their corresponding surveys (if applicable) with their corresponding due dates.
Ideally it would be a 3 column matrix where below the 'start window' header would be all the start window dates (where applicable, otherwise blank), below the 'due date' would be the the expiry(due) dates and below the 'end window'...you guessed it, the end window dates.
I know that i have to create measures for each date and i should not use the actual columns from the two fact tables however i am not sure how to do it. I read about asymetrical data which is probably what i have and need to deal with but also i am not familiar with the concept that would apply here.
Below is an inception of what i am trying to achieve and also below is a sample dataset.
There are also other dimensions which i have not included (these certificates belong to persons, so the person name may also be used instead of the certificate number and they also have names). The two tables are linked as per below
Certificates
Id | Certificate Number | Certificate Type | Department | Certificate Renewal Start Window | Certificate Expiry Date | Certificate Renewal End Window | SubjectToSurvey | SubjectToInterim |
45 | 1625-0037 | Permanent | QMS | 25-05-22 | 25-05-23 | 25-05-24 | TRUE | TRUE |
46 | OMB. 1625-0037 | Permanent | QMS | 18-02-24 | TRUE | TRUE | ||
47 | LPR0/GKK/20191031170813-E1 | Permanent | Technical | 25-02-23 | 25-05-23 | 25-08-23 | TRUE | TRUE |
48 | LPR0/GKK/20190913132217 | Permanent | Technical | 05-05-23 | 05-08-23 | 05-11-23 | TRUE | TRUE |
49 | LPR0/GKK/20191031170813-E1/B | Record | Technical | 25-05-23 | FALSE | TRUE | ||
50 | LPR0/GKK/201931847 | Record | Technical | 05-08-23 | FALSE | TRUE | ||
51 | DU-SVE-SO-00014070 | Inspection | Technical | 01-06-21 | FALSE | TRUE | ||
52 | 52205110 | Inspection | Technical | 25-09-21 | FALSE | TRUE | ||
53 | BOTTOM 7 | Other | Technical | 05-05-23 | 05-08-23 | 05-11-23 | TRUE | TRUE |
54 | BOTTOM 5 | Other | Technical | 25-05-21 | FALSE | TRUE | ||
55 | 2722 | Permanent | Technical | 18-03-23 | 18-06-23 | 18-09-23 | TRUE | TRUE |
56 | LPR0/VDO/20210310114547 | Permanent | Technical | 27-03-25 | 27-06-25 | 27-09-25 | TRUE | TRUE |
58 | SR-06689 | Inspection | Technical | 07-07-21 | FALSE | TRUE | ||
59 | BOTTOM 3 | Other | Technical | 26-07-23 | FALSE | TRUE | ||
61 | LPR0/VDO/20201207122426 | Permanent | Technical | 24-05-22 | 24-08-22 | 24-11-22 | TRUE | TRUE |
62 | LPR0/VDO/20201207122426/B | Record | Technical | 24-08-22 | FALSE | TRUE | ||
66 | 1932 | Record | Technical | 18-06-23 | FALSE | TRUE | ||
67 | 4318 | Inspection | Technical | 11-05-23 | FALSE | TRUE | ||
69 | 37 | Permanent | QMS | 13-02-20 | TRUE | TRUE | ||
70 | 2842 | Permanent | Technical | 18-06-23 | TRUE | TRUE | ||
71 | test | Maker | 06-05-24 | FALSE | TRU |
Surveys
Id | Survey Type | Survey Due Date | Survey Start Window Date | Survey End Window Date | MainCertRelatedID | Survey Status |
50 | Annual | 25-05-19 | 25-02-19 | 25-08-19 | 44 | Completed |
51 | Annual | 27-06-21 | 27-03-21 | 27-09-21 | 56 | Open |
52 | Annual | 27-06-22 | 27-03-22 | 27-09-22 | 56 | Open |
54 | Intermediate | 27-12-22 | 27-03-22 | 27-09-23 | 56 | Open |
56 | Annual | 27-06-24 | 27-03-24 | 27-09-24 | 56 | Open |
57 | Annual | 30-07-21 | 30-07-21 | 30-07-21 | 46 | Open |
58 | Annual | 25-05-20 | 25-02-20 | 25-08-20 | 47 | Completed |
59 | Annual | 05-08-19 | 05-05-19 | 05-11-19 | 43 | Completed |
60 | Annual | 05-08-20 | 05-05-20 | 05-11-20 | 43 | Completed |
61 | Intermediate | 05-02-21 | 05-05-20 | 05-11-21 | 43 | Open |
62 | Annual | 05-08-22 | 05-05-22 | 05-11-22 | 43 | Open |
63 | Annual | 05-08-19 | 05-05-19 | 05-11-19 | 48 | Completed |
65 | Annual | 05-08-20 | 05-05-20 | 05-11-20 | 48 | Completed |
66 | Intermediate | 05-02-22 | 05-05-21 | 05-11-22 | 48 | Open |
67 | Annual | 05-08-22 | 05-05-21 | 05-11-22 | 48 | Open |
70 | Annual | 25-05-20 | 25-02-20 | 25-08-20 | 44 | Completed |
73 | Intermediate | 25-11-20 | 25-02-20 | 25-08-21 | 44 | Completed |
76 | Annual | 25-05-21 | 25-02-21 | 25-08-21 | 44 | Open |
77 | Annual | 25-05-22 | 25-02-22 | 25-08-22 | 44 | Open |
78 | Annual | 21-12-20 | 21-12-20 | 21-12-20 | 45 | Open |
79 | Annual | 25-05-21 | 25-02-21 | 25-08-21 | 47 | Open |
81 | Intermediate | 25-11-20 | 25-02-20 | 25-08-21 | 47 | Open |
82 | Annual | 25-05-22 | 25-02-22 | 25-08-22 | 47 | Open |
83 | Annual | 27-06-23 | 27-03-23 | 27-09-23 | 56 | Open |
84 | Annual | 24-08-18 | 24-05-18 | 24-11-18 | 61 | Completed |
85 | Annual | 24-08-19 | 24-05-19 | 24-11-19 | 61 | Completed |
86 | Intermediate | 24-08-20 | 24-11-19 | 24-05-21 | 61 | Completed |
87 | Annual | 24-08-20 | 24-05-20 | 24-11-20 | 61 | Completed |
88 | Annual | 24-08-21 | 24-05-21 | 24-11-21 | 61 | Open |
89 | Annual | 05-08-20 | 05-05-20 | 05-11-20 | 43 | Completed |
90 | Annual | 05-08-21 | 05-05-22 | 05-11-22 | 48 | Open |
91 | Annual | 18-06-19 | 18-03-19 | 18-09-19 | 55 | Completed |
92 | Annual | 18-06-20 | 18-03-20 | 18-09-20 | 55 | Completed |
One thing i managed to do, is to have two matrix tables in the same report page and once you click on a certificate number, then it will filter the survey matrix and present the appropriate survey dates, but i don't really like this solution.
Pleased to hear anyone's thoughts on this
Solved! Go to Solution.
Yes, you need 6 measures: 3 for surveys and 3 for certificates. But whether just taking MIN and MAX of the relevant columns is enough depends on the requirements. If you've got a set of certificates in scope (meaning in the current context), then the MIN/MAX functions will return the min and max across these certificates. Same goes for surveys. Whether such a measure makes sense or not is a question of interpretation and business requirements. If, for instance, you have to have only one cert in scope to show such a measure, then you have to guard the calculation with something like
IF( HASOVEVALUE( T[CertificateID] ),
[Your Measure]
)
Again, same goes for surveys. It's up to you to decide what meaning you want to attach to the numbers and if you need to put in guard clauses.
Yes, you need 6 measures: 3 for surveys and 3 for certificates. But whether just taking MIN and MAX of the relevant columns is enough depends on the requirements. If you've got a set of certificates in scope (meaning in the current context), then the MIN/MAX functions will return the min and max across these certificates. Same goes for surveys. Whether such a measure makes sense or not is a question of interpretation and business requirements. If, for instance, you have to have only one cert in scope to show such a measure, then you have to guard the calculation with something like
IF( HASOVEVALUE( T[CertificateID] ),
[Your Measure]
)
Again, same goes for surveys. It's up to you to decide what meaning you want to attach to the numbers and if you need to put in guard clauses.
To my mind (and it can be easily seen in the picture as well), a measure such as "Earliest Certificate Renewal Start Window" does not depend on surveys. The other 2 measures you have in the first table visual do not depend on surveys, either. So, I don't quite understand where the problem is.
You just write
MIN( YourTable[Certificate Renewal Start Window] )
be done with it. The other measures are equally as simple.
I don't get this:
I know that i have to create measures for each date and i should not use the actual columns from the two fact tables
Why should you not use the columns from the fact tables? In fact, you may use columns from fact tables in DAX formulas and many a time you even should. On the other hand, you should never use them in the UI. Fact tables should almost always be hidden from users' view.
Thank you for your input. Perhaps i did not explain it correctly.
Since both certificates and surveys follow the "start window/due date/end window" pattern i wanted to create 3 such columns and below those, the corresponding dates would appear (where applicable).
The Rows would be
Certificate number
Survey
and their corresponding dates would appear under the columns (or blank if n/a)
So, i created a MIN measure as per your advise for all 6 dates and now i need 3 measures i guess (start/due/end) that would display the appropriate date measure of the 6 in the corresponding row. I Think that maybe a SWITCH measure is appropriate? Anyone to assist?
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |