Forum Discussion
Build summary visualization showing orders completed/issued in given week
Personally I would add a custom column to each table in the query editor with the name of the product, then append all tables into one. Any columns that don't exist across tables will have "null" values or blank for the tables where that column didn't exist. Then, I would also create a date table that spans the earliest and latest date from this master table.
After you create relationships between the date table and fact table (you will have one active and two passive relationships), you will need to create some measures depending on the relationship. With the active relationship, you could simply drag the relevant field to the visual and aggregate as a count, but for the inactive relationships you will need to create a measure using the USERELATIONSHIP function as an argument.
Learn more about date table:
Learn more about USERELATIONSHIP:
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Personally I would add a custom column to each table in the query editor with the name of the product, then append all tables into one. Any columns that don't exist across tables will have "null" values or blank for the tables where that column didn't exist. Then, I would also create a date table that spans the earliest and latest date from this master table.
After you create relationships between the date table and fact table (you will have one active and two passive relationships), you will need to create some measures depending on the relationship. With the active relationship, you could simply drag the relevant field to the visual and aggregate as a count, but for the inactive relationships you will need to create a measure using the USERELATIONSHIP function as an argument.
Learn more about date table:
Learn more about USERELATIONSHIP:
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Thank you! That sounds feasible - I'll try that out.