Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table called “observation” and amongst other things, I have “Date Opened”, “Date Closed” and RTMs”
What I am trying to do is to count how many items where open based on “Date Opened” but to filter out everything that is not RTM 1 and how many items where closed based on Closed Date and to filter everything out that is not RTM 1. Once completed, I wanted to present that data in Line Chart. Therfore, I have created new table with custom dates and index. Dates range from 9/30/15 – Today().
Then I have created relationship between ‘observation’ Date Opened and ‘open’ Date Open. I have created Measure as:
Measure RTM 1 Open = COUNTX ( FILTER ( 'observation', 'observation'[Date Opened] = RELATED ( 'Open'[Date Open] ) && ( 'observation'[RTMs] = "RTM 1" ) ), 'observation'[Date Opened] )
Then I have created column: RTM 1 Open = [Measure RTM 1 Open] to show me the data
I have created one more measure that would be aggregating over time to show Net open:
Measure RTM 1 Net OPEN = CALCULATE ( SUM ( 'Open'[RTM 1 Open] ) , FILTER ( ALL ( 'Open' ), 'Open' [Index] <= MAX( ( 'Open' [Index] ) ) ))
And to represent that data I have created column RTM 1 Net Open = [Measure RTM 1 Net OPEN]
Now since Date Closed is different than Date open, I have created new table called “Closed” with custom dates (same as already explained above).
I have also created relationship between ‘observation’ Date Closed and ‘Closed’ Date Closed. I have created Measure as:
Measure RTM 1 Closed = COUNTX ( FILTER ( 'observation', 'observation'[Date Closed] = RELATED ( 'Closed'[Date Closed] ) && ( 'observation'[RTMs] = "RTM 1" ) ), 'observation'[Date Closed] )
Then I have created column: RTM 1 Closed = [Measure RTM 1 Closed] to show me the data
I have created one more measure that would be aggregating over time to show Net closed:
Measure RTM 1 Net CLOSED = CALCULATE ( SUM ( 'CLOSED'[RTM 1 Closed] ) , FILTER ( ALL ( 'CLOSED' ), 'CLOSED'[Index] <= MAX( ( 'CLOSED'[Index] ) ) ))
And to represent that data I have created column RTM 1 Net CLOSED = [Measure RTM 1 Net CLOSED]
Now the issue is how do I present this data into the Line chart?
When I drop down “Date Open” from table “Open” and insert “RTM 1 Net Open” data looks perfectly fine… When I add “RTM 1 Net Closed”, data for closed issue would be not right and this is because (in my opinion) there is no relationship between tables “Open” and “Closed” and I am unable to create active relationship between the two because both of them already have relationship with “observation” table... Does anyone have a pointer how to solve this issue? It has been bugging me the whole day today and I just can't figure it out 😞
Solved! Go to Solution.
I have resolved this issue by creating one data table instead of multiple (one for Closed and one for Open)
I have resolved this issue by creating one data table instead of multiple (one for Closed and one for Open)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |