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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MadhuMj
New Member

I am trying to find Created and Resolved keys per week.

Sample DataSample Data

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

First create a separate date table that has a Date and Week column. The Dates rows should be a continuous series of values that cover the range of possible values in your Created and Resolved date columns. If you don't have a date table already you can use the CALENDAR() or CALENDARAUTO() functions to build this series of dates and then create a calculated column to get the week number plus any other columns you need.

 

Then create a relationship to the CD and RD columns (the relationship to the RD will be disabled as you can only have 1 active relationship at a time)

 

Then you create 2 measures (assuming your current table is called 'Table1')

 

Created Count= Countrows( 'Table1' )

 

Resolved Count = CALCULATE( COUNTROWS( 'Table1') , USERELATIONSHIP( 'Date'[Date], 'Table1'[RD] ) )

 

Then if you create a slicer on the week column and filter for week 41 you should see the values you want from the 2 measures above.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

You need to create one active and one inactive join with the date dimension.

Means both create date and resolve date will join with date of date dimension and then by using userelation you can choose the date to follow in a measure. Rest will be taken care by grouping or filter

 

Please refer example how can use userelation

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p...

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

d_gosbell
Super User
Super User

First create a separate date table that has a Date and Week column. The Dates rows should be a continuous series of values that cover the range of possible values in your Created and Resolved date columns. If you don't have a date table already you can use the CALENDAR() or CALENDARAUTO() functions to build this series of dates and then create a calculated column to get the week number plus any other columns you need.

 

Then create a relationship to the CD and RD columns (the relationship to the RD will be disabled as you can only have 1 active relationship at a time)

 

Then you create 2 measures (assuming your current table is called 'Table1')

 

Created Count= Countrows( 'Table1' )

 

Resolved Count = CALCULATE( COUNTROWS( 'Table1') , USERELATIONSHIP( 'Date'[Date], 'Table1'[RD] ) )

 

Then if you create a slicer on the week column and filter for week 41 you should see the values you want from the 2 measures above.

Thank you so much it worked. I am new to power BI and started learning by taking small scenarios.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.