Filter by two relationship

[mdelasheras]

Hi,

I'm having some troubles trying to filter by two relationships at the same time. I would like to filter a table with two date using a relationship to another date.

How can I count how many records do I have in a table where date1 and date2 are between my Calendar[date] filter??

I tried like that but got some errors:

Total = 
CALCULATE (
    COUNT ( PERIOD[Date1] ); 
    USERELATIONSHIP ( 'Calendar'[Date]; PERIOD[Date1] ) &&
    USERELATIONSHIP ( 'Calendar'[Date]; PERIOD[Date2] )     
)

[v-juanli-msft]

Hi @mdelasheras

Try this measure instead

records =
CALCULATE (
    COUNT ( PERIOD[Date1] ),
    FILTER (
        ALL ( PERIOD ),
        ( [Date1] <= MAX ( 'Calendar'[Date] ) )
            && ( [Date1] >= MIN ( 'Calendar'[Date] ) )
            && ( [Date2] <= MAX ( 'Calendar'[Date] ) )
            && ( [Date2] >= MIN ( 'Calendar'[Date] ) )
    )
)

Best Regards

Maggie

[mdelasheras]

Thanks @v-juanli-msft,

This measure works as intended except other filters are not being applied now.
I would like to show this measure in a column chart with a shared axis and also apply some visual filters.  Now it's displaying the same value regardless what my other filters are. 

For example: PERIOD[country] is a shared axis and PERIOD[TYPE] is a visual filter.

How can I solve this problem?

Thanks again!

[v-juanli-msft · Accepted solution]

Hi

As for your example, try this measure to see if it helps

Measure =
CALCULATE (
    COUNT ( PERIOD[Date1] ),
    FILTER (
        ALLEXCEPT ( PERIOD, PERIOD[type] ),
        ( [Date1] <= MAX ( 'Calendar'[Date] ) )
            && ( [Date1] >= MIN ( 'Calendar'[Date] ) )
            && ( [Date2] <= MAX ( 'Calendar'[Date] ) )
            && ( [Date2] >= MIN ( 'Calendar'[Date] ) )
    )
)

Best Regards

Maggie