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

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

     

2 Replies

  • 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.