Forum Discussion

mdelasheras's avatar
mdelasheras
Helper I
8 years ago
Solved

Filter by two relationship

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] )     
)

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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's avatar
      mdelasheras
      Helper I
      8 years ago

      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's avatar
        v-juanli-msft
        Community Support
        8 years ago

        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