Flag doublets in a calculated (SUMMARIZE) table?

[jayjay0306]

Hi people,

I need your help:

I have made a calculated table in Power BI Desktop

Table = SUMMARIZE('Sales','Sales'[CUSTOMER_KEY],Customer[Customer No],
				"Customer",NOT(ISBLANK(max(Customer[Customer No]))),
				"count",DISTINCTCOUNT('Sales'[CUSTOMER_KEY]),
				"Number of buying customers",DISTINCTCOUNT(Customer[Customer No]))

In this table I get doublet "Customer No" due to multiple "Customer keys"

No, my question is: How do I make a DAX script, which basically flags the doublets with the lowest Customer key?

I haver tried variuous things with no succes. One reason is I have a hard time trying to reference to another column in a calculated table?

What I need is somthing like this:

(or maybe "TRUE/FALSE", "1/empty".....anything will do)

Any ideas or leads will be greatly appreciated?

thanks.

Br,

JJ

[Anonymous · Accepted solution]

Hi  @jayjay0306 ,

Here are the steps you can follow：

1. Create calculated column.

Number of customers =
var _count=
COUNTX(FILTER(ALL(Sales),'Sales'[Customer_No]=EARLIER('Sales'[Customer_No])),[Customer_No])
var _max=
MAXX(FILTER(ALL('Sales'),'Sales'[Customer_No]=EARLIER('Sales'[Customer_No])),[CUSTOMER_KEY])
return
IF(
    'Sales'[CUSTOMER_KEY]=_max&&_count>1,1,
    IF(
        _count=1,    CALCULATE(DISTINCTCOUNT(Sales[Customer_No]),FILTER(ALL('Sales'),'Sales'[Customer_No]=EARLIER('Sales'[Customer_No]))),0))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jayjay0306]

Hi Liu Yang,

bloody brilliant! it works. Thanks 🙂

Br,

JayJay0306

[Anonymous]

Hi  @jayjay0306 ,

Here are the steps you can follow：

1. Create calculated column.

Number of customers =
var _count=
COUNTX(FILTER(ALL(Sales),'Sales'[Customer_No]=EARLIER('Sales'[Customer_No])),[Customer_No])
var _min=
MINX(FILTER(ALL('Sales'),'Sales'[Customer_No]=EARLIER('Sales'[Customer_No])),[CUSTOMER_KEY])
return
IF(
    'Sales'[CUSTOMER_KEY]=_min,_count,  CALCULATE(DISTINCTCOUNT(Sales[Customer_No]),FILTER(ALL('Sales'),'Sales'[Customer_No]=EARLIER('Sales'[Customer_No]))))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jayjay0306]

thanks Liu Yang,

I'm getting closer, but I have found out there are customers who appears more than twice, and then I get this:

Basically, what I need is this, for customers who appears more than once:

along with the customers who only appears once (as expected):

example:

in that way, I can simply SUM([Number of Customers]) and get the "number of customers", which is the goal.

thanks. Any solution is greatly appreciated.

br,

JayJay0306

[tamerj1]

Hi @jayjay0306 

you can creat a new calculated column in table as

Flag =
IF ( countrows ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer Number] )))> 1, 1,0 )