Measure without unpivot data?

[VuongLM93]

Dear all users,

Currently this is my problem, 

- I have a data of thousands of Customers with visit plan from Monday to Saturday on a weekly basis -data structured horizontally per customer (like the picture below)

- One customer might be visited by SalesMan from 1 to 2 time per Week

Now I now to create any kind of measure , it is best to unpivot this data. but it takes very long time .

For example how many customers are visited on Monday (and  similarly Tuesday, Wednesday...)

Creating 6 measures manually is OK but maybe not the best solution - Any idea?

[Anonymous]

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

[VuongLM93]

@Anonymous 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,

[Anonymous]

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

[parry2k]

@VuongLM93 if you don't want to unpivot your table, yes, you need to create measure for each day. If table is unpivoted then you need to create one measure and if there are any subsequent measures like compare from last week, compare with last year etc, it will be couple of measures but if you keep the data the way it is, any subsequent comparison will need measure for each day and it will be too many measures. In my opinion, unpivot is the way to go.