CODEWITHSUNDEEP
powerbi
Rhidium
Rhidium

Reputation: 55

Power BI: I can't seem to count the number of FPY records even though it selected the correct information

So I'm trying to get First Pass Yields count in Power BI. Just to be clear what FPY is suppose to get: FPY is checking within a specific date range for units that Passed the testing and haven't been teste before (meaning it truly tested the first time within that checked time period).

So I got some code to work to determine if the unit tested was first time run using the Measure function, but I can't seem to make it count the number of unique units found.

To keep it simple my example is going to just be checking if the unit has run for the first time within the time people selected.

With Slicer I have a date range of 1/9/2024 to 1/11/2024 In the Data Table I would have the following list:

Date Serial Status
01/10/2024 100001 PASS
12/01/2024 000002 FAIL
01/11/2024 000002 PASS
12/28/2024 000003 FAIL
01/09/2024 000003 PASS
01/09/2024 100004 FAIL
01/09/2024 100005 PASS
01/09/2024 100006 PASS
01/10/2024 100007 FAIL
01/11/2024 100007 PASS

The following code would add a "Yes" if I was looking at a table of this data. It was used to populate another Table.

IsFTY = 
VAR _mindate =
    CALCULATE (
        MIN( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Serial] = SELECTEDVALUE ( 'Table'[Serial] )
        )
    )
RETURN
    IF ( MIN ( 'Table'[Date] ) = _mindate, 1, BLANK () )

Table generated: (100002 and 100003 were "Blanked" since they were run the previous month) (100007 shows only the earliest test run)

Date Serial Status IsFTY
01/10/2024 100001 PASS 1
01/09/2024 100004 FAIL 1
01/09/2024 100005 PASS 1
01/09/2024 100006 PASS 1
01/10/2024 100007 FAIL 1
Total

So based on the results I am able to find the first time ever run serial # with the value of IsFTY as 1. However the total count is blank. When disabling the headers Date, Serial, Status, then the Table is shows IsFTY but the value is blank.. typically I would have expect a total count. In the table above you can see it doesn't total the value, hence I get the response below.

IsFTY

What I would have expected:

IsFTY 5

I did try modifying the code to use COUNT, COUNTROW, etc.. but I can't seem to get any of it working without an error. What I am asking is some help modifying my code to make it work, and learn from it. Note: I am a novice in PowerBI programming but do have some programming skills.

Note 2: I'm building to have a Table like this based on the above results. I'm not sure if this is an easier task, but this is really what I wanted as a response.

YYYY-MM Total FTY
2024-01 5 3

Thanks!

Upvotes: 1

Views: 55

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

The challenge you have is with SELECTEDVALUE. For totals and sub-totals, there isn't a selected value so this will return blank. What you need to do is evaluate each row and then do the count. This can be done with SUMX or similar.

Try the following:

IsFTY = 
  SUMX(
    'Table',
    (
      var thisFirstDate = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Serial]))
      return IF([Date] = thisFirstDate, 1)
    )
  )

The above is calculated per visible (filtered) row. ALLEXCEPT is another handy function which return ALL rows for the same serial.

Upvotes: 1

Related Questions