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

     

     

     

     

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

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

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

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      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