Forum Discussion

sivarajan21's avatar
sivarajan21
Post Prodigy
2 years ago
Solved

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

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 ...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • marcorusso's avatar
    marcorusso
    2 years ago

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

     

marcorusso's avatar
marcorusso
Most Valuable Professional
2 years ago

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's avatar
sivarajan21
Post Prodigy
2 years ago

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.