Best Way Possible To Add Specs

[cbruhn42]

So my original data file looks like this:

date - lot number - product type - attribute - value

My customer has a separate list of specs for product type and attributes.  I'd like to setup a spreadsheet to house the specs by product type and attribute, but also be able to keep track of when those specs change by  the date.  So that when a spec changes it will be reflected on my chart.

Any thoughts on how to do this and link the tables in Power BI?

[v-denglli-msft]

Hi @cbruhn42 ，

May I ask if you have gotten this issue resolved?

 If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .

Best Regards,
Dengliang Li

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

[AmiraBedh · Accepted solution]

I would go for creating a separate table for specs with the following columns:

- Product Type
- Attribute
- Spec Value
- Spec Change Date (to track when the spec was last updated)

This table will contain the specification values for each product type and attribute, along with the date when the spec was updated.

For your relationships :

- Link the Product Type column from FactData to DimSpecs on Product Type.
- Link the Attribute column from FactData to DimSpecs on Attribute.

You need a many-to-one relationship, where FactData (the many side) is linked to DimSpecs (the one side).

If you want to determine Spec Value for each date you may need to Create a CC or measure that pulls the spec value for the relevant date from DimSpecs.
You may want to filter based on the latest Spec Change Date before or equal to the data row date.

Spec At Date CC=
CALCULATE(
MAX(DimSpecs[Spec Value]),
FILTER(DimSpecs,
DimSpecs[Product Type] = FactData[Product Type] &&
DimSpecs[Attribute] = FactData[Attribute] &&
DimSpecs[Spec Change Date] <= FactData[Date]
)
)

You can then compare the actual value from your original data with the spec value:

Spec Difference = FactData[Value] - [Spec At Date]