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 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__cDate__cStatus__cFull_Status__cProject_Detail__cPart_Reference__cTotal_Cost__cPurchase_Order__cStatusWithProjectTraining_Date__c
a02Q300000500jVIAQ28/05/2024OOnboarding    O 
a02Q300000500jVIAQ29/05/2024OOnboarding    O 
a02Q300000500jVIAQ30/05/2024OOnboarding    O 
a02Q300000500jVIAQ31/05/2024OOnboarding    O 
a02Q300000500jVIAQ01/06/2024OOnboarding    O 
a02Q300000500jVIAQ02/06/2024OOnboarding    O 
a02Q300000500jVIAQ03/06/2024OOnboarding    O 
a02Q300000500jVIAQ04/06/2024OOnboarding    O 
a02Q300000500jVIAQ05/06/2024OOnboarding    O 
a02Q300000500jVIAQ06/06/2024OOnboarding    O 
a02Q300000500jVIAQ07/06/2024OOnboarding    O 
a02Q300000500jVIAQ08/06/2024TRTraining    TR 
a02Q300000500jVIAQ09/06/2024TRTraining    TR 
a02Q300000500jVIAQ10/06/2024TRTraining    TR 
a02Q300000500jVIAQ11/06/2024TRTraining    TR 
a02Q300000500jVIAQ12/06/2024TRTraining GWO BST - IN HOUSE2000a01Q300000L1o1tIABTR12-Jun-24
a02Q300000500jVIAQ13/06/2024TRTraining    TR 
a02Q300000500jVIAQ14/06/2024TRTraining GEV NFPA 70E Internal100a01Q300000LK1jsIADTR14-Jun-24
a02Q300000500jVIAQ15/06/2024TRTraining    TR 
a02Q300000500jVIAQ16/06/2024TRTraining    TR 
a02Q300000500jVIAQ17/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ18/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ19/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ20/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ21/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ22/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ23/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ24/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ25/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ26/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ27/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ28/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ29/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ30/06/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ01/07/2024LOn Project5790 - Project A   5790 
a02Q300000500jVIAQ02/07/2024LOn Project5790 - Project A   5790 

Any help would be appreciated

  • 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

     

     

     

     

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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.

  • 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