How to calculate distinct values in a column while summarising a calculated table declared as VAR

[Fuzzy_Barsik]

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:

• Sale date
• Cheque sum
• Cheque number
• Card number

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.

[Greg_Deckler]

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!!!! 🙂

[Fuzzy_Barsik]

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].

@Greg_Deckler 

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]

Hi there.

I did not say a thing about SUMMARIZECOLUMNS. If you want to create a table and store it in a VAR, then you should use the combination of SUMMARIZE/ADDCOLUMNS. And such a table can be used as a filter (if you're careful enough to keep lineage intact), or can be iterated over, or... you can do anything with it. If you want to restore lineage, you can do that as well - use TREATAS.

As to referring to columns of a table stored in a VAR. Yes, you can often refer to them in the form you typed but how you really should do it depends on how the column was obtained. Calculated columns can be referred to just by putting their names in square brackets: [ColumnName].

Best
D

[Fuzzy_Barsik]

@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.

[Greg_Deckler]

@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.

[Fuzzy_Barsik]

@Greg_Deckler 

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.

[Anonymous]

Hi @Greg_Deckler.

What about... "EARLIER is the greatest DAX function of all time. If you can't solve your calculation problem, just use EARLIER. Works every time."?

Looks like somebody's lying... LoL :)))

Best
D

[Anonymous]

You said something about not being able to use DISTINCTCOUNT... Well, you can use something different: DISTINCT ( <ColumnNameOrTableExpr> ). TableExpr can be ANY table expression, even one that returns a single column - think: SELECTCOLUMNS.

Best
D

[Anonymous]

Hi there.

Please, before you get too elated using SUMMARIZE, read these:

https://dax.guide/summarize/

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