Forum Discussion
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 date columns) as highlighted above. So I established one active relationship(between calendar Date to Communication table, Created_on date column) and one inactive relationship(between calendar Date to Communication table, Closed_on date column). But I am not sure whether it is correct or not.
My end goal is to create a visual like below that shows the trends of Created_on date col and Closed_on date col over calendar dates(like days, months, quarter, year)
So I created two simple 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 a couple of days now.
Because when I remove calendar date from visual, the counts are matching with data view:
The expected outcome 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 https://1drv.ms/u/s!AhUFhM5jR1jqijU8BO59-n3cgvJ4?e=KlhTP0
Please let me know if you need further details
Thanks in advance!
1) Created_On field in Communications is actually datetime.
Applying date format won't change datetime value, thus relationship with Date in calendar doesn't work, in the latest dates are with zero time.
You may create another column in Communications as
Create_On_Date = INT(Communications[Created_On]), apply date format if you wish (not necessary) and create relationship with calendar based on this field.
2) You don't need another relationship on Closed_On, above is enough
3) Not necessary but desirable to mark calendar as Date table (from 3-dots menu) with Date as a Key field; and uncheck Time Intelligence in options
Without that when you use time intelligence function PBI builds in background hidden date tables which overload the model and actually not under your control. It's enough to use Calendar table for that (marked as date table) taking into account it covers all periods.
Please note, if you've created measure with time intelligence most probably you need to re-create them with shifting on calendar as date table.
4) Measures could be
Queries Open = CALCULATE ( COUNTROWS ( Communications ), Communications[Closed_On] = BLANK () ) Queries Closed = CALCULATE ( COUNTROWS ( 'Communications' ), Communications[Closed_On] <> BLANK () )5) With that visual looks as
3 Replies
1) Created_On field in Communications is actually datetime.
Applying date format won't change datetime value, thus relationship with Date in calendar doesn't work, in the latest dates are with zero time.
You may create another column in Communications as
Create_On_Date = INT(Communications[Created_On]), apply date format if you wish (not necessary) and create relationship with calendar based on this field.
2) You don't need another relationship on Closed_On, above is enough
3) Not necessary but desirable to mark calendar as Date table (from 3-dots menu) with Date as a Key field; and uncheck Time Intelligence in options
Without that when you use time intelligence function PBI builds in background hidden date tables which overload the model and actually not under your control. It's enough to use Calendar table for that (marked as date table) taking into account it covers all periods.
Please note, if you've created measure with time intelligence most probably you need to re-create them with shifting on calendar as date table.
4) Measures could be
Queries Open = CALCULATE ( COUNTROWS ( Communications ), Communications[Closed_On] = BLANK () ) Queries Closed = CALCULATE ( COUNTROWS ( 'Communications' ), Communications[Closed_On] <> BLANK () )5) With that visual looks as
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 creating 'INT(Communication[created_on]' 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.
Excellove15 , you are welcome, glad to help
