topic Matrix Visualisation to filter on 2 date values in DAX Commands and Tips

Matrix Visualisation to filter on 2 date values

Richard_Halsall — Fri, 16 Aug 2024 15:47:52 GMT

I need help with the following requirement

I have the data model as shown

Fact supply is at the contractor and date level and details a contractors availability and training course date

I would like to create a matrix that allows filtering by training date but shows a different range of the contractors availability

e.g. show me those training courses taken by a contractor in June and that contractors availability in June & July

Data for the fact table

Contractor__c	Date__c	Status__c	Full_Status__c	Project_Detail__c	Part_Reference__c	Total_Cost__c	Purchase_Order__c	StatusWithProject	Training_Date__c
a02Q300000500jVIAQ	28/05/2024	O	Onboarding					O
a02Q300000500jVIAQ	29/05/2024	O	Onboarding					O
a02Q300000500jVIAQ	30/05/2024	O	Onboarding					O
a02Q300000500jVIAQ	31/05/2024	O	Onboarding					O
a02Q300000500jVIAQ	01/06/2024	O	Onboarding					O
a02Q300000500jVIAQ	02/06/2024	O	Onboarding					O
a02Q300000500jVIAQ	03/06/2024	O	Onboarding					O
a02Q300000500jVIAQ	04/06/2024	O	Onboarding					O
a02Q300000500jVIAQ	05/06/2024	O	Onboarding					O
a02Q300000500jVIAQ	06/06/2024	O	Onboarding					O
a02Q300000500jVIAQ	07/06/2024	O	Onboarding					O
a02Q300000500jVIAQ	08/06/2024	TR	Training					TR
a02Q300000500jVIAQ	09/06/2024	TR	Training					TR
a02Q300000500jVIAQ	10/06/2024	TR	Training					TR
a02Q300000500jVIAQ	11/06/2024	TR	Training					TR
a02Q300000500jVIAQ	12/06/2024	TR	Training		GWO BST - IN HOUSE	2000	a01Q300000L1o1tIAB	TR	12-Jun-24
a02Q300000500jVIAQ	13/06/2024	TR	Training					TR
a02Q300000500jVIAQ	14/06/2024	TR	Training		GEV NFPA 70E Internal	100	a01Q300000LK1jsIAD	TR	14-Jun-24
a02Q300000500jVIAQ	15/06/2024	TR	Training					TR
a02Q300000500jVIAQ	16/06/2024	TR	Training					TR
a02Q300000500jVIAQ	17/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	18/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	19/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	20/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	21/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	22/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	23/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	24/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	25/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	26/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	27/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	28/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	29/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	30/06/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	01/07/2024	L	On Project	5790 - Project A				5790
a02Q300000500jVIAQ	02/07/2024	L	On Project	5790 - Project A				5790

Any help would be appreciated

Re: Matrix Visualisation to filter on 2 date values

lbendlin — Fri, 16 Aug 2024 22:32:15 GMT

filters on columns in the same table are applied on top of each other. You seem to want independent filters. That requires the use of disconnected calendar tables to feed your slicers.

Re: Matrix Visualisation to filter on 2 date values

Anonymous — Mon, 19 Aug 2024 03:25:27 GMT

Hi Richard_Halsall ,

First of all, thank you lbendlin for the quick reply. Did the reply help you solve your problem? If so, please mark it as a solution. If not, you can try the following:

(1) Create a column.

_StatusWithProject = VALUE( IF('Table'[Project_Detail__c]=BLANK(),"0",[StatusWithProject]))

(2) Create a date table.

Date = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

(3)

If I misunderstand your needs, please clarify them in a follow-up reply.

Best Regards,

Neeko Tang

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

Re: Matrix Visualisation to filter on 2 date values

Richard_Halsall — Mon, 19 Aug 2024 09:37:01 GMT

Hi Anonymous lbendlin , thank you both for your input, I tested both solutions and made progress but still do not have exactly what I require.

Using the disconnected calendar method I have created the TrainingDates table and a Training Dates slicer and this measure

Active Training Dates =

var dateselectmin = minx(allselected(TrainingDates),TrainingDates[Date])

VAR dateselectmax = MAXX(allselected(TrainingDates),TrainingDates[Date])

RETURN

CALCULATE(

DISTINCTCOUNT(FactSupply[Contractor__c]),

FILTER(ALL(

FactSupply),

FactSupply[Training_Date__c] <= dateselectmax && FactSupply[Training_Date__c] >= dateselectmin

)

Which returns the correct number of unique technicians with a training date between the Training Dates slicer dates when I place the measure in a card

My requirement is that these 8 technicians only show in the matrix when the status date range is selected - I have tried using the Active Training Dates measure as a visual filter but it doesn't work

Any help would be appreciated. Thanks

Re: Matrix Visualisation to filter on 2 date values

lbendlin — Mon, 19 Aug 2024 13:07:05 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Matrix Visualisation to filter on 2 date values

Richard_Halsall — Mon, 19 Aug 2024 13:37:24 GMT

File can be found at this link

https://we.tl/t-Ij9EPvBJ9X

Thanks

Re: Matrix Visualisation to filter on 2 date values

lbendlin — Mon, 19 Aug 2024 23:40:56 GMT

Active Training Dates = CALCULATE( DISTINCTCOUNT(FactSupply[Contractor__c]), TREATAS(values(TrainingDates[Date]),FactSupply[Training_Date__c]) )

I don't think I fully understand the premise - feel free to modify