cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ioniaman
Regular Visitor

Matrix table with dates for two related tables

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.

 

Ioniaman_0-1620754469248.png

 

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

Ioniaman_1-1620754890430.png

 

 

 

 

Certificates

 

IdCertificate NumberCertificate TypeDepartmentCertificate Renewal Start WindowCertificate Expiry DateCertificate Renewal End WindowSubjectToSurveySubjectToInterim
451625-0037PermanentQMS25-05-2225-05-2325-05-24TRUETRUE
46OMB. 1625-0037PermanentQMS 18-02-24 TRUETRUE
47LPR0/GKK/20191031170813-E1PermanentTechnical25-02-2325-05-2325-08-23TRUETRUE
48LPR0/GKK/20190913132217PermanentTechnical05-05-2305-08-2305-11-23TRUETRUE
49LPR0/GKK/20191031170813-E1/BRecordTechnical 25-05-23 FALSETRUE
50LPR0/GKK/201931847RecordTechnical 05-08-23 FALSETRUE
51DU-SVE-SO-00014070InspectionTechnical 01-06-21 FALSETRUE
5252205110InspectionTechnical 25-09-21 FALSETRUE
53 BOTTOM  7OtherTechnical05-05-2305-08-2305-11-23TRUETRUE
54 BOTTOM  5OtherTechnical 25-05-21 FALSETRUE
552722PermanentTechnical18-03-2318-06-2318-09-23TRUETRUE
56LPR0/VDO/20210310114547PermanentTechnical27-03-2527-06-2527-09-25TRUETRUE
58SR-06689InspectionTechnical 07-07-21 FALSETRUE
59BOTTOM  3OtherTechnical 26-07-23 FALSETRUE
61LPR0/VDO/20201207122426PermanentTechnical24-05-2224-08-2224-11-22TRUETRUE
62LPR0/VDO/20201207122426/BRecordTechnical 24-08-22 FALSETRUE
661932RecordTechnical 18-06-23 FALSETRUE
674318InspectionTechnical 11-05-23 FALSETRUE
6937PermanentQMS 13-02-20 TRUETRUE
702842PermanentTechnical 18-06-23 TRUETRUE
71testMaker  06-05-24 FALSE

TRU

 

Surveys

 

IdSurvey TypeSurvey Due DateSurvey Start Window DateSurvey End Window DateMainCertRelatedIDSurvey Status
50Annual25-05-1925-02-1925-08-1944Completed
51Annual27-06-2127-03-2127-09-2156Open
52Annual27-06-2227-03-2227-09-2256Open
54Intermediate27-12-2227-03-2227-09-2356Open
56Annual27-06-2427-03-2427-09-2456Open
57Annual30-07-2130-07-2130-07-2146Open
58Annual25-05-2025-02-2025-08-2047Completed
59Annual05-08-1905-05-1905-11-1943Completed
60Annual05-08-2005-05-2005-11-2043Completed
61Intermediate05-02-2105-05-2005-11-2143Open
62Annual05-08-2205-05-2205-11-2243Open
63Annual05-08-1905-05-1905-11-1948Completed
65Annual05-08-2005-05-2005-11-2048Completed
66Intermediate05-02-2205-05-2105-11-2248Open
67Annual05-08-2205-05-2105-11-2248Open
70Annual25-05-2025-02-2025-08-2044Completed
73Intermediate25-11-2025-02-2025-08-2144Completed
76Annual25-05-2125-02-2125-08-2144Open
77Annual25-05-2225-02-2225-08-2244Open
78Annual21-12-2021-12-2021-12-2045Open
79Annual25-05-2125-02-2125-08-2147Open
81Intermediate25-11-2025-02-2025-08-2147Open
82Annual25-05-2225-02-2225-08-2247Open
83Annual27-06-2327-03-2327-09-2356Open
84Annual24-08-1824-05-1824-11-1861Completed
85Annual24-08-1924-05-1924-11-1961Completed
86Intermediate24-08-2024-11-1924-05-2161Completed
87Annual24-08-2024-05-2024-11-2061Completed
88Annual24-08-2124-05-2124-11-2161Open
89Annual05-08-2005-05-2005-11-2043Completed
90Annual05-08-2105-05-2205-11-2248Open
91Annual18-06-1918-03-1918-09-1955Completed
92Annual18-06-2018-03-2018-09-2055Completed

 

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

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

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.

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

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.

View solution in original post

daxer
Solution Sage
Solution Sage

@Ioniaman 

 

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.

Ioniaman
Regular Visitor

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?

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors