cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors