Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jameshoneywill
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 UIProduct GroupPeriodAttributeValue
22Beans01/01/2023Profit5
22Beans01/01/2023Profit5
22Beans01/01/2023Profit5
33Toast01/01/2023Profit10
33Toast01/01/2023Profit10
55Friut01/01/2023Profit20
22Beans01/01/2023Volume Units1
22Beans01/01/2023Volume Units1
22Beans01/01/2023Volume Units1
33Toast01/01/2023Volume Units1
33Toast01/01/2023Volume Units1
55Fruit01/01/2023Volume Units1
22Beans01/02/2023Profit5
22Beans01/02/2023Profit5
22Beans01/02/2023Profit5
33Toast01/02/2023Profit10
33Toast01/02/2023Profit10
55Fruit01/02/2023Profit20
22Beans01/02/2023Volume Units1
22Beans01/02/2023Volume Units1
22Beans01/02/2023Volume Units1
33Toast01/02/2023Volume Units1
33Toast01/02/2023Volume Units1
55Fruit01/02/2023Volume Units1



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 GroupPeriodRequired result
Beans01/01/20231
Toast01/01/20232
Fruit01/01/20231
Beans01/02/20231
Toast01/02/20232
Fruit01/02/20231


I have tried the following as a calculated column 

//
Value (Qty adj) =

 

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
 



1 REPLY 1
lbendlin
Super User
Super User

lbendlin_0-1680303392646.png

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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