Forum Discussion

angieleman's avatar
angieleman
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Measure to filter a gantt chart based on two dates

I have a table of activities. There is an activity type column, a start date column and an end date column.  I also have a Dates table, which is summarized to months and has as Month_Year column. Th...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi angieleman ,

     

    Please try to delete the relationship between Dates and Combo table. Then create measure.

     

    Measure = 
var MinDate = MIN(Dates[FirstofMonth])
var MaxDate = MAX(Dates[LastofMonth])
var tab = 
    CALCULATETABLE(
        VALUES(Combo[activities]),
        FILTER(
            'Combo',
            'Combo'[Activity End Date] >= MinDate 
            && 'Combo'[Activity Start Date] <= MaxDate
        )
    )
return COUNTROWS( INTERSECT( tab, VALUES(Combo[activities]) ) )

     

    Filter activities column by Measure:

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi angieleman ,

 

Please try to delete the relationship between Dates and Combo table. Then create measure.

 

Measure = 
var MinDate = MIN(Dates[FirstofMonth])
var MaxDate = MAX(Dates[LastofMonth])
var tab = 
    CALCULATETABLE(
        VALUES(Combo[activities]),
        FILTER(
            'Combo',
            'Combo'[Activity End Date] >= MinDate 
            && 'Combo'[Activity Start Date] <= MaxDate
        )
    )
return COUNTROWS( INTERSECT( tab, VALUES(Combo[activities]) ) )

 

Filter activities column by Measure:

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

angieleman's avatar
angieleman
Icon for Advocate I rankAdvocate I
5 years ago

Thanks Winniz, I'm sure that would work and its nice to have options. I ended up creating this measure:

List of IDActType values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Combo'[IDActType])

RETURN
CALCULATE(
        CONCATENATEX(
            VALUES('Combo'[IDActType]),
            'Combo'[IDActType],
            ", ",
            'Combo'[IDActType],
            ASC
        ),
FILTER('Combo','Combo'[Activity End Date]>=[MinDate] && 'Combo'[Activity Start Date]<=[MaxDate]))
 
I added this as the filter on my visual and selected "is not blank" and it worked.