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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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])

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.