Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Icon for Helper IV rankHelper IV
2 years ago
Solved

Matrix Visualisation to filter on 2 date values

Hi 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 ...
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    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

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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. 

Richard_Halsall's avatar
Richard_Halsall
Icon for Helper IV rankHelper IV
2 years ago

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