Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
kcantor
Community Champion
Community Champion

@Anonymous 

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 Super User!




Anonymous
Not applicable

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. 

 

ZunzunUOC
Resolver III
Resolver III

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.