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

Trying to count Perfect Lines

I have tried many different formulas and I cannot seem to come up with the answer on how to do this.

 

I have 4 Tables:

  1. Inspection Table (entry for each widget that fails)
    1. Line
    2. Production ID
    3. Failure Mode
    4. Date of failure
  2. Production Scan Table (entry for each widget that is made)
    1. Line
    2. Date of Production
    3. Count of widgets made
  3. Area Table (list of lines and what area they belong to)
    1. Line
    2. Area (east, west...)
  4. Key Measures Table (all measures)
    1. Total Failed Widgets = DISTINCTCOUNT('INSPECTION TABLE'[PRODUCTION ID])
    2. Total Produced = SUM('PRODUCTION SCAN TABLE'[COUNT OF WIDGETS MADE])

Relationships:

  • 'Area Table'[Line]  1------>*  'Inspection Table'[Line]
  • 'Area Table'[Line]  1------>*  'Production Scan Table'[Line]

 

I have a visual where I list the lines from the Area Table. I am filtering if the Total Failed Widgets = 0 and the Total Produced Widgets is >0. This gives me a list of the perfect lines. 

 

I need a visual and formula that counts how many perfect lines are listed and it needs to change based on the date filters on the visual/page/report. 

 

Please help!

4 REPLIES 4
ZunzunUOC Member
Member

Re: Trying to count Perfect Lines

Could you show your raw data and your expected result? Thaks.

kweibelt Regular Visitor
Regular Visitor

Re: Trying to count Perfect Lines

This is a copy of what I currently have with an if statement in Line Active/Pass Testm. The expectation is that the graph should show 5 total for lines that are active (with produced units >0) and pass (with no failed inspections).

 

Capture.JPG

kcantor Super Contributor
Super Contributor

Re: Trying to count Perfect Lines

@kweibelt 

Have you considered using your measures to create calculated columns and creating a calculation based upon that to allow for your count?If you built them as columns your measure would be something along the lines of CALCULATE(DISTINCTCOUNT(Area Table[Line]), Area Table [Total Failed Widgets] = 0 && Area Table[Total Produced Widget] >= 0)



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

Proud to be a Datanaut!




kweibelt Regular Visitor
Regular Visitor

Re: Trying to count Perfect Lines

I created 2 columns in the Area Table: Total Failed Widgets and Total Produced Widgets. Then I did the measure as you have below and I receive this error message:

 

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression. 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors