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

[grggmrtn]

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?

[grggmrtn · Accepted solution]

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]

@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

[grggmrtn]

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

[amitchandak]

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