Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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] ) )
)
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] ) )
)
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.
User | Count |
---|---|
83 | |
69 | |
68 | |
66 | |
53 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |