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

Frequent Visitor

## Distinctcount in an if statement with conditions

Hi

My fact table contains multiple rows for the same item every month. I have a measure that sums the total quantity based on when the attribute is Volume Units

What I am looking for is a distinct count of the Product UIN when the product is beans

This is the fact table;

 Product UI Product Group Period Attribute Value 22 Beans 01/01/2023 Profit 5 22 Beans 01/01/2023 Profit 5 22 Beans 01/01/2023 Profit 5 33 Toast 01/01/2023 Profit 10 33 Toast 01/01/2023 Profit 10 55 Friut 01/01/2023 Profit 20 22 Beans 01/01/2023 Volume Units 1 22 Beans 01/01/2023 Volume Units 1 22 Beans 01/01/2023 Volume Units 1 33 Toast 01/01/2023 Volume Units 1 33 Toast 01/01/2023 Volume Units 1 55 Fruit 01/01/2023 Volume Units 1 22 Beans 01/02/2023 Profit 5 22 Beans 01/02/2023 Profit 5 22 Beans 01/02/2023 Profit 5 33 Toast 01/02/2023 Profit 10 33 Toast 01/02/2023 Profit 10 55 Fruit 01/02/2023 Profit 20 22 Beans 01/02/2023 Volume Units 1 22 Beans 01/02/2023 Volume Units 1 22 Beans 01/02/2023 Volume Units 1 33 Toast 01/02/2023 Volume Units 1 33 Toast 01/02/2023 Volume Units 1 55 Fruit 01/02/2023 Volume Units 1

this is what I want (i.e. beans only return 1 per month as there is a distinct count performed on the product UID but the others are just a sum of their quantity

 Product Group Period Required result Beans 01/01/2023 1 Toast 01/01/2023 2 Fruit 01/01/2023 1 Beans 01/02/2023 1 Toast 01/02/2023 2 Fruit 01/02/2023 1

I have tried the following as a calculated column

//

IF(

fact[Attribute] = "Volume Units"

&&

(fact[Product Group] = "Beans"

Distinctcount(Product UID)

,

fact_combined[Value])

//

followed by a measure;

CALCULATE(sumx( fact, Value (Qty adj) fact_combined[Attribute] = "Volume (Units)")

but what is returned in the calculated column in a count of every time that Product UID appears in the whole fact table (I have more months) so I get massive figures

I'm certain a calculated column isn't the way to go now but not sure how to approach this

Many thanks

James

Super User

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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors