I have a slowly changing dimension and a Fact Table
I'm interested in seeing for a selected date ( in the second table I have only the end of month) the number of products for each category and the sales from the second table.
For example if I choose 31/01/2020 I want to see only Category C2 - sales 100 and Category C3- sales 500 (1 product each).
How can I manage StartDate-EndDate in the first table and assign the correct category for every product of the second table?
You could create a measure as below:
MAX ( Table1[Category] ),
Table1[Product] = SELECTEDVALUE ( Table2[Product] )
&& SELECTEDVALUE ( Table2[Date] ) >= Table1[StartDate]
&& SELECTEDVALUE ( Table2[Date] ) <= Table1[EndDate]
and here is sample pbix file, please try it.
Thank you very much,
but if I have another 2-3 fact table containing product-sales-date and I have to put all informations toghether?
And the dimension doesn't contain only the category, but some attributes also (for example color and so on)---
Is it necessary to create a measure for every attribute,including every fact table in the formula?
I don't know the best way to use a slowly changing dimension in a pbix.
You may need to create the relationship among these tables. please refer to this document:
If you still have the problem, Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.