Need help creating additional rows in matrix table that fall under two criteria

[julesdude]

Hi all,

I have the following two tables in my data model:

Table1 - Leases

Asset Reference	Lease Reference	Unit Reference	Commencement Date	Expiration Date	Termination Date	Lease Ended	Unit Start Date	Unit End Date	Unit Ended
REF01	S007	REF01-20	01-Dec-14	31-Dec-21	31-Dec-21	True	31-Jan-22		False
REF01	S023	REF01-19	01-Mar-22	28-Feb-29		False	01-Jan-21		False
REF01	S021	REF01-18	01-Jan-21	31-Dec-32		False	01-Jan-21		False
REF01	S017	REF01-17	11-Oct-19	31-Mar-26		False	11-Oct-19		False
REF01	S015	REF01-15	01-Oct-11	30-Sep-23		False	01-Oct-11		False
REF01	S014	REF01-14	01-Jan-12	31-Dec-20	31-Dec-20	True	01-Jan-12	31-Dec-20	True
REF01	S013	REF01-13	01-Apr-13	31-Mar-26		False	01-Apr-13		False
REF01	S012	REF01-12	01-Apr-12	31-Mar-24		False	01-Apr-12		False
REF01	S011	REF01-11	01-Sep-11	31-Aug-23		False	01-Sep-11		False
REF01	S010	REF01-10	01-Sep-13	31-Aug-27		False	01-Sep-13		False
REF01	S009	REF01-09	15-May-18	14-May-30		False	15-May-18		False
REF01	S020	REF01-08	01-Jul-20	30-Jun-32		False	01-Jul-20		False
REF01	S008	REF01-08	01-Sep-15	31-Aug-27	30-Jun-20	True	01-Jul-20		False
REF01	S007	REF01-07	01-Dec-14	31-Dec-21	31-Dec-21	True	01-Dec-14		False
REF01	S024	REF01-07	04-Feb-22	03-Feb-28		False	01-Dec-14		False
REF01	S006	REF01-06	01-Apr-19	31-Dec-20	31-Dec-20	True	01-Jan-21		False
REF01	S022	REF01-06	01-Jan-21	31-Dec-32		False	01-Jan-21		False
REF01	S005	REF01-05	15-Jun-17	30-Nov-27	30-Jun-20	True	01-Jul-20		False
REF01	S019	REF01-05	01-Jul-20	30-Jun-32		False	01-Jul-20		False
REF01	S004	REF01-04	15-Jun-17	14-Jun-29	30-Jun-20	True	01-Jul-20		False
REF01	S018	REF01-04	01-Jul-20	30-Jun-33	30-Jun-22	True	01-Jul-20		False
REF01	S025	REF01-04	01-Jul-22	30-Jun-35		False	01-Jul-20		False
REF01	S003	REF01-03	08-Aug-18	07-Aug-30	02-Dec-21	True	08-Aug-18		False
REF01	S002	REF01-02	01-Aug-15	22-Nov-21	22-Nov-21	True	01-Aug-15		False
REF01	S001	REF01-01	01-Sep-18	31-Aug-30		False	01-Sep-18		False

Table2 - Units

Asset Reference	Unit Reference	Unit Number	Unit Start Date	Unit End Date
REF01	REF01-09	20	15-May-18
REF01	REF01-03	13	08-Aug-18
REF01	REF01-01	11	01-Sep-18
REF01	REF01-08	19	01-Jul-20
REF01	REF01-05	16	01-Jul-20
REF01	REF01-04	14+15	01-Jul-20
REF01	REF01-06	17	01-Jan-21
REF01	REF01-02	12	01-Aug-15
REF01	REF01-20	18.2	31-Jan-22
REF01	REF01-07	18.1	01-Dec-14
REF01	REF01-13	38-39-40-41	01-Apr-13
REF01	REF01-12	37	01-Apr-12
REF01	REF01-11	36	01-Sep-11
REF01	REF01-10	35	01-Sep-13
REF01	REF01-18	42	01-Jan-21
REF01	REF01-19	43	01-Jan-21
REF01	REF01-14	42-43	01-Jan-12	31-Dec-20
REF01	REF01-15	Antenna	01-Oct-11
REF01	REF01-17	38-39-40-41 (board)	11-Oct-19

I have merged the Unit Start Date and Unit End Date columns of Table2 into Table1 using Power Query and created in Table1 two boolean columns which say whether a lease has ended (Expiration Date is in the past or Termination Date is in the past from today's date) or unit has ended (Unit End Date is in the past).

In my report, I want to list everything as it appears in Table 1 into a Matrix Table visualisation. However, I don't want to display any rows where Lease Ended is True or Unit Ended is True. That's fine - as I can use the filter in the report to filter these out.

However, what my table doesn't show is that although a Unit Number may have a lease that has come to an end or been terminated, the unit still exists (there is no Unit End Date against it) and so it is vacant. I want the report to show 'vacant' as the Lease Reference in this case. 

What would be the best approach to do this? Is it a case of adding a column for Table2 to show whether that unit has no lease dates still current and then somehow referencing this status to create the row? Do i need to create a table entirely in DAX to achieve this?

In my report I'm getting a list of 15 items showing the Asset Reference, Unit Reference and Lease Reference against it - but these are all active leases. There are 3 more rows that I want showing for the 3 units that are there but have no active leases against them and are vacant.

[v-mengzhu-msft]

Hi @julesdude ,

I'm sorry but I'm not sure I fully understand what you are saying, it might be easier for me to understand your needs if you could attach a picture of what you want the intended effect to be.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[julesdude]

Hi @v-mengzhu-msft 

Thanks for responding. I think the best way to tackle this is to create a DAX column or measure (whichever makes more sense) with the following logic:

1. Take the Asset Reference 

2. Take the Unit Reference 

3. Filter by Asset Reference and Unit Reference so that it matches the same Asset Reference and Unit Reference for that row, and filter Unit Ended column by False results

4. For the above filtered results, count number of 'True' results from column [Lease Ended]

5. Return count number

This way if there is a count number greater than 0 we know that for this asset's unit number, the unit is not ended, and we have a Lease Ended = True (a end lease date or terminated lease date is in the past relative to today's date).

How do I achieve this in DAX?