Customer Retention

@nipunadv, that looks absolutely frightening…

So here’s something to think about before you move on because I can’t make out anything or zoom into the depicted model but it looks like a “one model to answer all your questions” which isn’t a good design principle.

Each model should be designed to answer a small set of specific (related) questions.

Only bring what you absolutely need and strip away all redundant columns from your tables.

DAX is optimized for star schema’s maybe you can de-normalize other tables as well to create a single DIM table like combining the CALENDAR_CYCLES and CALENDAR-DAYS in order to get a single Date dimension table which has a unique key at the lowest granularity, the date level.
Note that for DAX time intelligence functions to work there are a number of other requirements for this specific dimension table, see this post here.

In your sample file you had BLANKS in the CUSTOMER Dim table, a Key should be unique and shouldn’t contain blanks - that will prevent a 1:M relationship

Modelling, unless you have a really deep understanding of the implications of using M:M relationships (see post here) and you can’t avoid it with bridge tables for example - then be my quest - in all other cases go the extra mile to create a 1:M relationship.

To understand the flow of filters throughout a model, place them in a waterfall like fashion. All DIM tables on top and all FACT tables below. Hide all Key fields (in DIMs) not meant for filtering or visualization AND hide all Keys in FACTs, no exceptions. (see post here)

I hope this is helpful.

2 Likes