Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cshier
New Member

Comparing Date Counts

Hi,

I appreciate that this is a question that has been asked and answered many times before but despite my best efforts in looking in to date tables, I have not been able to wrap my head around it.

I'd like to compare two counts of dates along a timeline, a started date and and end date, while keeping date hierarchies available. 

cshier_2-1680201629479.png

 

cshier_1-1680201535704.png

Desired output: Jan should have 4 started, 2 ended, and Feb should have 3 started, 5 ended. 

It's very likely something simple but any help in connecting the dots will be appreciated, beginner PowerBI user here. 

 

Onedrive link to pbix file: https://capilanou1-my.sharepoint.com/:u:/g/personal/cshier_capilanou_ca/EUc08R9JKd1EqXxUXBLde9EBW8A7...

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

I wouldn't say it is simple but it is something to learn to understand relationships and DAX.

First you want the date table to filter the Fact table so change the relationship to one-way.

The important thing, though, is to activate the relationship when using the 'End' measure.  So you'll write an explicit measure to get the Count of end records while using the relationship which is inactive currently.  

It'll be a simple Calculate with count and the keyword USERELATIONSHIP.

Here's an example

https://www.learndax.com/power-bi-userelationship-function-with-example/ 

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

I wouldn't say it is simple but it is something to learn to understand relationships and DAX.

First you want the date table to filter the Fact table so change the relationship to one-way.

The important thing, though, is to activate the relationship when using the 'End' measure.  So you'll write an explicit measure to get the Count of end records while using the relationship which is inactive currently.  

It'll be a simple Calculate with count and the keyword USERELATIONSHIP.

Here's an example

https://www.learndax.com/power-bi-userelationship-function-with-example/ 

Beautiful! That was exactly the missing piece! Thank you for that. I was starting to lose my mind and you have restored my sanity for now.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors