Matrix table with dates for two related tables

[Ioniaman]

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

[Anonymous · Accepted 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.

[Anonymous]

@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]

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?