cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Smart Count If

Hi,

I'll just go ahead with an example of what I need to do to illustrate what I want.

 

Line IDLine's Constant
10.23
20.15
30.325
40.14
50.314

 

Line IDStop Reason
1Reason A
2Reason B
3Reason C
4Reason D
5Reason X
1Reason Y
5Reason Z
4Reason A
2Reason B
5Reason C
1Reason D
4Reason Y
3Reason A
3Reason Z
1Reason A
2Reason B


I want to represent "Line ID" VS the value I get from multiplying (The count of each "Stop Reason" for each Line ID * the Line's Constant"

 

The only problem is, I don't want to to do it using the following or a similar count if function.

 

Count of Stop Reason = COUNTROWS(
FILTER(
RELATEDTABLE('Raw Data'),
'Raw Data'[Stop Reason] = "Reason Y"
)
)

 

and so on, because I have more than 100 unique values in the "Stop reason" coulmn.

 

Is there a smarter why to do that?

 

Thanks a lot.

 

1 ACCEPTED SOLUTION
Community Support
Community Support

@NaderSaeed 

Sheet7 is the table with constant, Shee8 is the table with stop reason.

 

Result = 
var count_= CALCULATE(COUNTROWS(Sheet8),ALLEXCEPT(Sheet8,Sheet8[Line ID],Sheet8[Stop Reason]))
var constant =  CALCULATE(MAX(Sheet7[Line's Constant]),FILTER(Sheet8,Sheet8[Line ID]=RELATED(Sheet7[Line ID])))
Return count_*constant

 


Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

 

View solution in original post

4 REPLIES 4
Community Support
Community Support

@NaderSaeed 

Sheet7 is the table with constant, Shee8 is the table with stop reason.

 

Result = 
var count_= CALCULATE(COUNTROWS(Sheet8),ALLEXCEPT(Sheet8,Sheet8[Line ID],Sheet8[Stop Reason]))
var constant =  CALCULATE(MAX(Sheet7[Line's Constant]),FILTER(Sheet8,Sheet8[Line ID]=RELATED(Sheet7[Line ID])))
Return count_*constant

 


Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

 

View solution in original post

Brilliant. Exactly what I wanted!

Thank you so much Paul. 🙂

Super User I
Super User I

Can you share the PBIX?

Thanks for replying.

Unfortunately I can't, as its data is confidential.

But the example I gave roughly illustrates what I need to do.

 

For example,

For Line 1, Reason Z.

I want to multiply (0.23 * Count of Reason Z for Line 1), and do the same for each Line ID vs each Stop Reason.

i.e. Line 1, Reason A, Line 1, Reason X, and so on.

 

And then represent the data as follows, where the numbers on the graph are the values I get after the aforementioned multiplication for each line and reason.

 

Untitled.png

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors