A few fact tables in one model, the model is mostly based on dates. How do I show a count of rows in one table by month for date A and date B?

ThePewSpecialist — Tue, 13 Jun 2023 06:16:31 GMT

I have this model that's based on a system. The system contains no values but rather the complexity is in terms of the amount of dates everywhere. One table can contain 5 relevant dates and depending on the criteria another one would be used. Currently it's built in Power BI where I use inactive relationships with USERELATIONSHIP() in CALCULATE().

The model itself is also a bit funny, there's two fact tables that usually aren't related but sometimes they are. Dimensions are also many to one to the fact. Conclusion, this is a pretty backwards system and breaks most best practices. Can't really do anything about is as that's how the external system is built.

My problem that I'm currently struggling with is how to show two counts in one chart based on different dates.

Date A	Date B	Value
1/Jan/2023	1/Feb/2023	Hello
1/Jan/2023	1/Feb/2023	World

How do I create a chart where the Jan 2023 bar has a count of 2 and Feb 2023 bar has a count of 2?

The only solution I've thought of is to concatenate the table for each value and create a 'Type of Date' column, but since there's 5 dates this seems pretty inefficient. The model doesn't contain that many rows, at most 5,000 per table.

Re: A few fact tables in one model, the model is mostly based on dates. How do I show a count of rows in one table by month for date A and date B?

marcus_sommer — Tue, 13 Jun 2023 10:33:47 GMT

Concatenating fact-tables to develop a data-model in the direction of a star-scheme is the official recommended way to build a working, simple & easy to create and use and performant data-model.

In your case it seems to be a matter of a few minutes to build it - and then you could start to check the data and playing with various kinds of views and maybe adjusting the data-model to more advanced calendars by including flags, offset-values, continuous running fields and similar stuff and/or adding as-of-tables.

topic Re: A few fact tables in one model, the model is mostly based on dates. How do I show a count of rows in one table by month for date A and date B? in App Development

A few fact tables in one model, the model is mostly based on dates. How do I show a count of rows in one table by month for date A and date B?

Re: A few fact tables in one model, the model is mostly based on dates. How do I show a count of rows in one table by month for date A and date B?