Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cv02011
New Member

fact table startdate -enddate

I have a slowly changing dimension and a Fact Table

ProductCategoryStartDateEndDate
P1C101/01/202020/01/2020
P1C221/01/202031/03/2020
P2C301/01/202031/12/2099

 

ProductSalesDate
P110031/01/2020
P115029/02/2020
P130031/03/2020
P250031/01/2020

 

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?

Thank you

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi  @cv02011 

You could create a measure as below:

Measure =
CALCULATE (
    MAX ( Table1[Category] ),
    FILTER (
        Table1,
        Table1[Product] = SELECTEDVALUE ( Table2[Product] )
            && SELECTEDVALUE ( Table2[Date] ) >= Table1[StartDate]
            && SELECTEDVALUE ( Table2[Date] ) <= Table1[EndDate]
    )
)

Result:

4.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Thank you 

hi  @cv02011 

You may need to create the relationship among these tables. please refer to this document:

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

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

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

Well, with the sample data, you would do this:

Table2 Category = MAXX(FILTER('Table1'[Product] = 'Table2'[Product] && 'Table2'[Date]>='Table1'[StartDate] && 'Table2'[Date]<='Table1'[EndDate]),[Category])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors