I want to create a summary table that would look like this.
Customer number, year of sale, sum(sales amount).
Up to now this is fine with the summarize dax command.
- I want to make sure that a year without a sale, my customer shows as 0.
So I am missing the
I was going to create two distinct table (one for customer and one for year) and make an left outer join from customer to year, then add a column with the sales summary (default to 0) but there might be a better solution.
1. First of all, you should have a proper calendar that's connected to your fact table. Please learn about a proper Date table in Power BI.
2. Then you should slice by the calendar's attributes (like months, for instance) and not by fields in the fact table. You should also have a Customer dimension connected to your fact table. The rule is that one shouldn't slice by anything that's directly embedded in the fact table, only by dimensions. There are many GOOD reasons for this.
3. You can get 0 instead of BLANK if you write your measure as:
I'm glad it works for you but... you should not (actually NEVER) put a full table fact table as a filter for a measure. This is because if you do that, then you are putting not only the full fact table in there but also the expanded version of the table and this can and will prove to be very expensive (think: slow) on a very big fact table. The golden rule of DAX is: Never filter a table if you can filter a column. That's not the only reason why you should not filter by a full table but to explain all the perils I'd have to write a whole chapter of a book. Don't have time to do this.