Forum Discussion

cbruhn42's avatar
cbruhn42
Helper III
1 year ago
Solved

Best Way Possible To Add Specs

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?

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

    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]

     

2 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    1 year ago

    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]

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.