Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all.
I have a source of sales data containing over 60 million rows, which includes all customers' cheques over years, be they Loyalty Programme participants or not. This table contains among othes the following fields:
If a cheque doesn't belong to a Loyalty Programme participant, "0000000000000000" value is assigned to 'Card number'.
The table 'CARDS AND THEIR OWNERS' contains all card numbers issued over years and customers' IDs. A customer may possess several cards. The table has one-to-many relation with 'ALL CHEQUES' table.
I need to build some aggregated reports on Loyalty Programme participants sales. If I didn't need a report on their visits quantity, everithing would be fine. The following SUMMARIZE formula works well:
SUMMARIZE(
FILTER('ALL CHEQUES';
'ALL CHEQUES'[Card number] = RELATED('CARDS AND THEIR OWNERS'[Card number]));
'ALL CHEQUES'[Sale date];
'ALL CHEQUES'[Shop ID];
"Revenue"; SUM('ALL CHEQUES'[Cheque sum]);
"Cheques Qty"; COUNTA('ALL CHEQUES'[Cheque number]);
"Cards By Visit Qty"; DISTINCTCOUNT('ALL CHEQUES'[Card number]))
However I do need.
In order to keep my .pbix file smaller than 1 GB I want to avoid storing filtered Loyalty Programme participants sales as a calculated table and try to perform all calculations in VARs. I created 'tLoyaltySales' table with no problems:
VAR tLoyaltySales = SELECTCOLUMNS(
FILTER('ALL CHEQUES';
'ALL CHEQUES'[Card number] = RELATED('CARDS AND THEIR OWNERS'[Card number]));
"Sale date"; 'ALL CHEQUES'[Sale date];
"Card number"; 'ALL CHEQUES'[Card number];
"Contact Key"; RELATED('CARDS AND THEIR OWNERS'[Contact Key]);
"Shop"; 'ALL CHEQUES'[Shop ID];
"Cheques Qty"; COUNTA('ALL CHEQUES'[Card number]);
"Revenue"; 'ALL CHEQUES'[Cheque sum])
However stumbled over inability to calculate DISTINCTCOUNT while summarising tLoyaltySales table. I read through a lot of threads but didn't get a direct answer to my questions (found several nice tricks though). Eventually I came up with the following brilliant (as I thought) formula:
VAR tSalesSummary = SUMMARIZE(tLoyaltySales;
[Sale date];
[Shop];
"Revenue"; SUMX(tLoyaltySales; [Cheque sum]);
"Cheques Qty"; SUMX(tLoyaltySales; [Cheques Qty]);
"Cards By Cheques Qty"; COUNTROWS(SUMMARIZE(tLoyaltySales;
[Sale date];
[Card number];
"Revenue"; SUMX(tLoyaltySales; [Cheque sum])));
"Card Owners By Cheques Qty"; COUNTROWS(SUMMARIZE(tLoyaltySales;
[Sale date];
[Contact Key];
"Revenue"; SUMX(tLoyaltySales; [Cheque sum]))))
A little thing is that instead of such a nice diagram
it turns into this with enormous amount of zeros
Any help is appreciated.
Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Little trouble following this but you might try something along the lines of:
VAR tLoyaltySales = SELECTCOLUMNS(
FILTER('ALL CHEQUES';
'ALL CHEQUES'[Card number] = RELATED('CARDS AND THEIR OWNERS'[Card number]));
"Sale date"; 'ALL CHEQUES'[Sale date];
"Card number"; 'ALL CHEQUES'[Card number];
"Contact Key"; RELATED('CARDS AND THEIR OWNERS'[Contact Key]);
"Shop"; 'ALL CHEQUES'[Shop ID];
"Cheques Qty"; COUNTA('ALL CHEQUES'[Card number]);
"Revenue"; 'ALL CHEQUES'[Cheque sum])
VAR tSalesSummary =
SUMMARIZE(tLoyaltySales;
[Sale date];
[Shop];
"Revenue";
SUMX(tLoyaltySales; [Cheque sum]);
"Cheques Qty"; SUMX(tLoyaltySales; [Cheques Qty]);
"Cards By Cheqes Qty"; COUNTROWS(DISTINCT(SELECTCOLUMNS(tLoyaltySales,"Card number",[Card number])));
"Cards By Cheqes Qty"; COUNTROWS(DISTINCT(SELECTCOLUMNS(tLoyaltySales,"Contact key",[Card number])));
)
And bonus, 100% untested!!!! 🙂
Hi all.
@Anonymous
Yes, I understand that the issue is related to context filters. However SUMMARIZECOLUMNS is not applicable as a solution, 'cos I can't reference a column in a table defined in VAR via 'Table Name'[Column Name].
Unfortunately DISTINCT + SELECTCOLUMNS combination doesn't work either, 'cos DAX can't see columns in a table defined in VAR.
Here is the .pbix sample file.
All data are invented. 🙂
@Anonymous
Yes, you didn't say a thing about SUMMARIZECOLUMNS. The article you referenced did.
Not sure I follow the rest of your comment. Yes, it's possible to reference a calculated colum of a VAR table via brackets, if the function recognises the context, and I do exploit it where possible - you can see it either in my sample file or my sample code in original post. The point is that DAX functions for a particular calculation I need don't recognise the context, and I still looking for a solution how to outmanoevre that. So any help is appreciated.
@Fuzzy_Barsik , I am still not clear on exactly what you are going for. What would be the expected output that you desire for your sample PBIX? I have the sample PBIX but I really don't understand what you are trying to accomplish.
Correct figures in aggregated report.
So as to be able to compare and contrast:
- Total Sales vs Loyalty Sales (i.e. revenue generated by Loyalty Programme participants) over time
- Total Cheques quantity va Loyalty Cheques quantity over time
- Cards usage over time
- Amount of Loyalty Programme participants visits over time
and so on and so forth.
Hi there.
Please, before you get too elated using SUMMARIZE, read these:
and here's the article that shows why it should only be used for getting the unique combinations of rows:
https://www.sqlbi.com/articles/all-the-secrets-of-summarize/
Even though at the beginning of the article you'll see it says it's obsolete as of January 2018, it's not as obsolete as you might think. The Dax Guide says that the Name and Expression parameters in this function are not only deprecated but also not recommended for a reason...
Best
D
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |