Forum Discussion
Helper IStacked Bar Chart not showing correct values
Hi all, I have an issue. I have a stacked bar having on X axis Semester by Year from the Calendar Table, on the Y axis a measure that counts the current open risks based on the Risk Table. In the legend, Current Rating from the Risk Table.
The issue is that the rating changes whenever there is a change in the Current Rating in the Risk Table each semester instead of keeping the actual count each semester.
Here an example of the tables:
Risk Table
| Risk Id | Creation Date | Current Rating for current semester |
| 4 | 30/06/2023 | Medium |
| 1 | 30/06/2022 | High |
| 2 | 31/12/2022 | Low |
| 3 | 30/06/2023 | Medium |
Manually, every semester the current Rating for current semester is substitute with a new rating, and the old rating is stored in the Rating Table:
| Risk Id | Semester Date | Rating |
| 1 | 30/06/2022 | Low |
| 1 | 31/12/2022 | Medium |
| 2 | 31/12/2022 | Low |
| 2 | 30/06/2023 | High |
| 3 | 30/06/2023 | Medium |
| 3 | 31/12/2023 | Medium |
| 4 | 30/06/2023 | Medium |
If I use the Rating column from the Rating table in the legend, I get this:
On the data model side, the Calendar Date joins the Risk rating and the Risk table, and the Risk Rating actively joins the Risk table on the Risk Id.
Any help is much appreaciated.
2 Replies
- amitchandak
Super User
Sonnet , I think calendar should join to risk Rating and Risk should also join to risk rating
Try meausre like
Count of Risks by Historical Rating =
CALCULATE(
COUNTROWS('Rating'),
FILTER(
'Rating Rating',
'Rating Rating'[Risk Id] = 'Rating'[Risk Id]
&& 'Rating Rating'[Semester Date] <= MAX('Calendar Table'[Date])
)
)
SonnetWhen using your measure above, at this point:
'Rating Rating'[Risk Id] = 'Rating'[Risk Id]the Rating[Risk Id] is not selectable. Only measures are displayed.
Any other way to solve it please?
