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
grggmrtn
Post Patron
Post Patron

Star schema with a factless fact table - a new "keyless" table got added but I need to access it...

I have the perfect star schema going on in our dataset - seriously, it's beautiful 🙂 I just hope I can explain my problem just as beautifully...

Our fact table is "factless", meaning it only contains the primary keys to all the dimensions. All calculations are done through relationships and measures. 

BUT

A new table just got thrown at us, and I'm not quite sure what to do with it. It contains the prices of our products, and when the prices were active - something like this:

DistributorProductPriceStartPriceStopPrice
VKClothing01.01.202231.12.2022164,73
VKFood01.01.202131.12.2021157,22
VKFood01.01.202231.12.2022162,23
HGClothing01.01.202230.06.2022122,34
HGClothing01.07.202231.12.2022125,66
HGFood01.07.202131.12.2021145,89
HGFood01.01.202130.06.2021123,44
HGFood01.01.202230.06.2022156,44

As you can see, prices for the same products can differ depending on the Distributor and date.

Using relations for this seems like a real challenge, considering that our distributor is in one dimension table, the products in another, and of course there's the date table...

So what I need is the price per distributor per product by date. I'll be making calculations in my report by all three things (the price data will be used for billing).

Does anyone have any ideas as to how I can go about doing this?

1 ACCEPTED SOLUTION
grggmrtn
Post Patron
Post Patron

I actually found the answer @amitchandak - and it might be what you were trying to tell me...

I first expanded the PriceStart and PriceStop columns, creating a row ([Date]) for each date between the two. It created a much larger table, but one which was much easier to work with.

Then I created a new column in my fact table, using LOOKUPVALUE to insert the price into it

LOOKUPVALUE (
    Prices[price],
    Prices[Date], RELATED(Date[Date]),
    Prices[Distributor], RELATED(Distributor[DistributorName]),
    Prices[Product], RELATED(Product[ProductName])
)

It worked like a charm.

View solution in original post

4 REPLIES 4
grggmrtn
Post Patron
Post Patron

I actually found the answer @amitchandak - and it might be what you were trying to tell me...

I first expanded the PriceStart and PriceStop columns, creating a row ([Date]) for each date between the two. It created a much larger table, but one which was much easier to work with.

Then I created a new column in my fact table, using LOOKUPVALUE to insert the price into it

LOOKUPVALUE (
    Prices[price],
    Prices[Date], RELATED(Date[Date]),
    Prices[Distributor], RELATED(Distributor[DistributorName]),
    Prices[Product], RELATED(Product[ProductName])
)

It worked like a charm.

amitchandak
Super User
Super User

@grggmrtn , You can use this table to populate data in fact. First of add the keys here.

 

Then using those keys (equal to ) and between join of dates, create a new column in fact for price

 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

I'm sorry, you lost me at the very beginning?

@grggmrtn , Please share expected output, see If I can get it

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