cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DistinctCount IF?

Hi All,

 

I'm new to Power BI (and DAX) coming from Tableau.

 

I've got a question regarding the DiscountCount function.

 

I need to calculate a percentage of orders that include a certain item. The way my data is structured is line items on every order for the last year. (Example: If one order has three items on it, three lines are returned for that order each line with a specific item).

 

I'd like to create a measure that give me the sum of a distinct count of order numbers but only if they include a certain item number. I can't use the filter function because I'd need to compare the afore mentioned measure to a count of all distinct orders.

 

This seems like something that should be very doable, I'm just can't wrap my head around it yet.

 

Thanks!

 

PS - Does anyone have any good resources (perferably books) that can get me into the swing of using DAX and Power BI?

1 ACCEPTED SOLUTION
greggyb
Resident Rockstar
Resident Rockstar

CALCULATE(
    DISTINCTCOUNT( FactOrder[OrderNumber] )
    ,FactOrder[Line Item] = <certein item number>
)

View solution in original post

6 REPLIES 6
greggyb
Resident Rockstar
Resident Rockstar

CALCULATE(
    DISTINCTCOUNT( FactOrder[OrderNumber] )
    ,FactOrder[Line Item] = <certein item number>
)

@greggyb  @KHorseman  I am trying to use your formula, but i am not sure where I am doing a mistake 

 

want to calculate distinct job number on gloss pack option or body colour option 

gp201641_0-1663832549810.png

 


Here is the error 

gp201641_1-1663832627437.png

 

 

@greggyb  Can you please tell me where i am doing wrong, I tried to use your formula 

 

gp201641_0-1663832381534.png

 

Anonymous
Not applicable

Worked perfectly! Thank you so much!

If you want to understand the reason behind that answer, check this out: http://www.powerpivotpro.com/2009/12/powerpivot-dax-calculate-is-a-supercharged-sumif/

 

Actually the title of that blog post is slightly misleading; CALCULATE is a supercharged WHATEVERIF. COUNTIF, SUMIF, DIVIDEIF, etc. depending on what kind of expression you feed into it. That whole blog is a fantastic resource for learning DAX, and they've got a great book as well.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thank for the resource, I really appreciate it!

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.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors