cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Create Measured for Unique Customer Number by Product

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.

8 REPLIES 8
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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

Thanks.

Super User

The brackets are incorrectly placed.  Check my earlier reply.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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.

Super User

Remove [ before Data

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@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")

Regular Visitor

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

Regular Visitor

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")

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors