cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Measure that counts names column "A" which have only one/single occurance in column "B"

Hi,

what function shall i use for a measure that dcounts names column "Name" which have only one/single occurance in column "Product".

what I have:

I want my measure to show:
Dcount of names that have only apples (BBB and DDD) = 2.

I can solve this with 2 steps but this way seems a bit coplicated.

1. adding an extra column "C" that shows how many distinct products each Name has;
2. and then creating a measure e.g. calculate(distinctcount(NAME), C = 1, Product = "apple". C = 1 means that eg DDD has only 1 product.

1 ACCEPTED SOLUTION
Community Support

Hi, @Kudy

In fact, your original solution is sufficiently general and straightforward. The code I provide below is just to give you a different idea.

``````Measure1 =
VAR tab1 =
SUMMARIZE (
'Table',
'Table'[Name],
"first_product", MIN ( 'Table'[Product] ),
"last_product", MAX ( 'Table'[Product] )
)
RETURN
COUNTROWS (
FILTER ( tab1, [first_product] = "apple" && [last_product] = "apple" )
)

``````

Best Regards,
Community Support Team _ Eason

3 REPLIES 3
Super User

HI @Kudy

Try this:

``Measure = calculate(distinctcount(table[name]),filter(all(table),table[product]="apple"))``

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

Helper I

Hi Vahid,

I tried your solution but unfortunately did not get the result I needed. It gave me = 3 instead of 2

Community Support

Hi, @Kudy

In fact, your original solution is sufficiently general and straightforward. The code I provide below is just to give you a different idea.

``````Measure1 =
VAR tab1 =
SUMMARIZE (
'Table',
'Table'[Name],
"first_product", MIN ( 'Table'[Product] ),
"last_product", MAX ( 'Table'[Product] )
)
RETURN
COUNTROWS (
FILTER ( tab1, [first_product] = "apple" && [last_product] = "apple" )
)

``````

Best Regards,
Community Support Team _ Eason

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.