cancel
Showing results 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

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:

 Distributor Product PriceStart PriceStop Price VK Clothing 01.01.2022 31.12.2022 164,73 VK Food 01.01.2021 31.12.2021 157,22 VK Food 01.01.2022 31.12.2022 162,23 HG Clothing 01.01.2022 30.06.2022 122,34 HG Clothing 01.07.2022 31.12.2022 125,66 HG Food 01.07.2021 31.12.2021 145,89 HG Food 01.01.2021 30.06.2021 123,44 HG Food 01.01.2022 30.06.2022 156,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
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.

4 REPLIES 4
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.

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

Post Patron

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

Super User

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors