Forum Discussion
Visualizing Historical Data
Hi tom-lenzmeier ,
In my example, I used the IsCurrent flag in the table to determine the version. I added a column where IsCurrent = 1 is labeled as Version 1 (current record), and IsCurrent = 0 as Version 0 (historical record).
I also transformed the data in Power Query by unpivoting columns like StartDate, EndDate, CurrentPrice, and PriceValidFrom, so these attributes are displayed as rows.
For the matrix visual, I set:
Rows: Attributes
Columns: Version
Values: AttributeValue
I disabled row and column subtotals so only the values appear side by side in the matrix.
Since your table already uses the SCD Type 2 structure (StartDate, EndDate, IsCurrent) as described in the documentation, this approach should work for you as well.
If you need more details, just let me know.
Hi tom-lenzmeier ,
I tried to reproduce the scenario using a small sample dataset and was able to achieve the expected result.
For better understanding, I’m sharing a screenshot of the sample matrix visual that shows the attributes as rows and the agreement versions as columns.
FYI:
Please take a look and let me know if this aligns with what you are trying to achieve, or if any additional clarification is needed.
Thank you.
Yes! That's it. Can you make a suggestion about how you versioned the records? Also, what properties did you set for the matrix? I'm familiar with a type 2 slowly changing dimension using this method: https://learn.microsoft.com/en-us/fabric/data-factory/slowly-changing-dimension-type-two
Hi tom-lenzmeier ,
In my example, I used the IsCurrent flag in the table to determine the version. I added a column where IsCurrent = 1 is labeled as Version 1 (current record), and IsCurrent = 0 as Version 0 (historical record).
I also transformed the data in Power Query by unpivoting columns like StartDate, EndDate, CurrentPrice, and PriceValidFrom, so these attributes are displayed as rows.
For the matrix visual, I set:
Rows: Attributes
Columns: Version
Values: AttributeValue
I disabled row and column subtotals so only the values appear side by side in the matrix.
Since your table already uses the SCD Type 2 structure (StartDate, EndDate, IsCurrent) as described in the documentation, this approach should work for you as well.
If you need more details, just let me know.
Thank you so much.
