Forum Discussion
Helper IIIMeasure without unpivot data?
First of all, please learn a bit about dimensional modeling on which Power BI depends for good and performant models (you can find very good material about PBI model design on YT). When you've already done that, then you will know that the format you need for your data is this:
CustomerID|VisitDay
1,2019-01-01
1,2019-01-02
1,2019-01-05
2,2019-01-02
2,2019-01-03
....
Then you'll build a proper Date table which will cover all the (full) years that appear anywhere in your model. The granularity being "the day." You'll then join this table in a 1:many fashion to your VisitDay field. Once this is in place, you'll create a measure:
[Customer Count] = DISTINCTCOUNT ( T[CustomerID] )
From this you'll be able to build everything you need and you only need 1 measure to do it.
By the way, you don't unpivot your data in DAX. You do it in Power Query. This is the ultimate ETL tool to shape your data. DAX is for calculations ONLY and let it sink in - CALCULATIONS ONLY.
Best
Darek
Helper IIIAnonymous Thanks for your reply and suggestion,
I do understand can use unpivot , however it makes the data a little more heavy. Especially when refreshing the data model,
Well, either you'll pay the price at refresh time or query time - you have to choose which one is more important to you.
Secondly, please note that fact tables should be narrow and long, whereas dimensions should be wide and short. That's the rule.
Do not ever be tempted to cut corners with models in PBI. If you do and the design is not correct, you'll be (very) sorry rather sooner than later. You've been warned.
Best
Darek