Create Measured for Unique Customer Number by Product

[allenpee85]

Hi all, need help.

How can I make an Indicator (Expected Result) based on unique Customer Number and Type of property.

E.g. Each unique customer number will be group by property type, customer 111 having Shop and Bungalow, this is not expected result, because it consist both types of property.

But only unique Customer Number with only Shop property type will be taken.

What is the Power BI measures can use here?

Thank you.

 

[Ashish_Mathur]

Hi,

Try this calculated column formula

=if(calculate(distinctcount(Data[Type]),filter(Data,[Data[Customer Number]=earlier(Data[Customer Number])))=1,"Yes","No")

Hope this helps.

[allenallen]

Hi @Ashish_Mathur 

Have tried to paste the formula, seems there is error, could help to fix?

Thanks.

[Ashish_Mathur]

The brackets are incorrectly placed.  Check my earlier reply.

[allenallen]

Hi @Ashish_Mathur ,

Sorry to bother you again, copy and paste the formula provided....

=if(calculate(distinctcount(Data[Type]),filter(Data,[Data[Customer Number]=earlier(Data[Customer Number])))=1,"Yes","No")

The results as below....

Can help to relook which part did i miss?

Thank you for your kind assistance.

[Ashish_Mathur]

Remove [ before Data

[amitchandak]

@allenpee85 , A measure

=

var _1 = countx(filter(allselected(Table) , Table[Customer] =max(Table[Customer]) ) , Table[Customer])
return
if(_1=1, "Yes", "No")

or a new column

new column =

var _1 = countx(filter((Table) , Table[Customer] =earlier(Table[Customer]) ) , Table[Customer])
return
if(_1=1, "Yes", "No")

[allenallen]

Hi @amitchandak ,

Thanks for your kind assistance, tried the formula and it works.

var _1 = countx(filter(allselected(Table) , Table[Customer] =max(Table[Customer]) ) , Table[Customer])
return
if(_1=1, "Yes", "No")

Can i trouble you to help add a condition to indicate "YES" only for Customer Number 444 - with "Shop" property?

Rest of type e.g. Terrace, Bungalow still shows "No".

Thank you in advance.

[allenallen]

Hi @amitchandak 

Have also tried your formula, but a bit error... could you help me to see the formula again?

Wanted to upload the PBI file, but cannot....

Many thanks!

var _1 = countx(filter(allselected(Table) , Table[Customer] =max(Table[Customer]) ) , Table[Customer])
return
if(_1=1, "Yes", "No")