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
Solved! Go to Solution.
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.
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
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.
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.
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |