cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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

Report. 01.PNG

 

it turns into this with enormous amount of zeros

Report. 02.PNG

 

Any help is appreciated.

9 REPLIES 9
Highlighted
Anonymous
Not applicable

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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

Highlighted
Super User IV
Super User IV

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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

Highlighted
Anonymous
Not applicable

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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
Highlighted
Helper I
Helper I

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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

Highlighted
Anonymous
Not applicable

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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
Highlighted
Super User IV
Super User IV

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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
Highlighted
Helper I
Helper I

Re: How to calculate distinct values in a column while summarising a calculated table declared as VA

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors