Relationship issue when Calendar table filtering two date columns in fact table

[sivarajan21]

Hi,

I have a model where Calendar table filters communication table as below:

Here Calendar table Date column should filter communication table  Created_on and Closed_on (both columns) as highlighted above. So I established one active relationship(between calendar Date to Communication Created_on date column)  and one inactive relationship(between calendar Date to Communication Closed_on date column). But I am not sure whether it is correct or not.

My end goal is create a visual like below that shows the trend of Created_on and Closed_on over calendar dates.

So I created two dax measures named Open Queries and Closed Queries:

Open Queries =
COUNTBLANK ( Communications[Closed_On] )

Closed Queries =
CALCULATE (
( COUNT ( Communications[Closed_On] ) ),
Communications[Closed_On] <> BLANK ()
)

But the resulting count in visual doesn't match with the data in data view for communication table

Is there any issue with my relationships or dax? I am confused and its bothering for for a couple of days now.

Because when I remove calendar date from visual, the counts are matching with data view:

The expected out come is when using calendar date month, days, and year to filter above measures, the count should match the data in data view.

PFA files here Communications 4 (1).pbix

Please let me know if you need further details

Thanks in advance!

@Ahmedx @Greg_Deckler @amitchandak @Ashish_Mathur @marcorusso 

[marcorusso · Accepted solution]

It depends on what you want.
You have two dates, which means that a query is open in all the days between open and close. If you want to be able to count that in any date, use this pattern: Events in progress – DAX Patterns

If you want to count how many queries were opened and closed in a day/month/quarter (regardless of when they have been closed), you should write:

Opened Queries = COUNTROWS ( Communications )
Closed Queries =
CALCULATE (
    COUNTROWS ( Communications ),
    USERELATIONSHIP ( Communications[Closed_On], Calendar[Date] ),
    KEEPFILTERS ( NOT ISBLANK ( Communications[Closed_On] ) )
)

[sivarajan21]

@marcorusso 

Dear Sir,

Thank you so much for your help with this beautiful solution!

Wow! this is best solution I would have asked for and many thanks!

I apologize for the delayed response and I appreciate your patience!

The reason for delay is I wanted to test the report fully so that it did not cause any issues later on.

Your guidance has really helped me resolve the doubts.

I found your explanation on Events in progress – DAX Patterns very insightful.

If there's anything I can assist you with in the future, please don't hesitate to ask.

Thanks again for your support, it means a lot to me.