Distinct Count based on values and first date

[TcT85]

I have a complicated calculation that needs to be visualised.

Here is an example of data.

Product	Serial Number	Test ID	Status	Date
A	11111	ID 888	FAIL	2023-01-11
A	11111	ID 888	PASS	2023-01-19
A	22222	ID 888	PASS	2023-01-19
A	33333	ID 888	PASS	2023-01-19
A	44444	ID 888	PASS	2023-01-19
A	44444	ID 888	PASS	2023-01-25
B	55555	ID 999	FAIL	2023-01-25
B	55555	ID 999	PASS	2023-01-26
B	66666	ID 999	PASS	2023-01-29
C	77777	ID 888	PASS	2023-02-01
C	77777	ID 999	PASS	2023-02-02
C	77777	ID 999	PASS	2023-02-20

• From this data I need to distinct count the serial number over the dates
• The same serial number cannot be recounted on multiple date.
• The serial number should only be counted on column Status "PASS".

For example, serialnumber 11111 is shown on date 2023-01-11 with status "FAIL"  and 2023-01-19 with status PASS".

Result: The serial number has to be counted as 1 and on date 2023-01-19.

• The serial number date has to defined by first date with status pass on the latest date TEST ID 

For example the serial number 77777 has ID888 with dates 2023-02-01, 2023-02-02 and ID999 with date 2023-02-20 and all status are PASS. 

Result: The serial number has to be counted as 1 over time and should be counted on date 2023-02-02

The pareto graph needs to result like this image:

Another graph that i need will require Product on X-axis instead of Date.

Is this possible to do in Power BI?

[TcT85]

Hi Yolo Zhu,

I'm now trying with incremental refresh with transform data instead of direct query but with limited data.

But I'm still investigating if this incremental refresh with tranform data will actually work with big set of data.

Your suggestion works perfectly fine with incremental refresh with Transform data.

But I'm having issue with a follow up dax expression with your suggestion included.

[SerialNumber TestID Filter] is your suggestion in the DAX expression below.

TLOG Field Return PPM1 =

VAR FactoryCount = CALCULATE ( DISTINCTCOUNT( custClaimItem[intSerialNo] ), returnType[ReturnTypeTextEng] = "Field Return" )

VAR PCBSerialNrDist = SUM(TLOG_ProductionTest_view[SerialNumber TestID Filter])

VAR FactoryPPM = ((FactoryCount/PCBSerialNrDist)*1000000)

Return

IF(

    FactoryPPM = 0,

    BLANK(),

    FactoryPPM)

On 2023 jan 11 I get Infinity data, on 2023 jan 19 i get 1000000ppm which is correct.

How do I get rid of the Infinity data??

[v-xinruzhu-msft]

Hi @TcT85 

You can refer to the following suggestion.

1.Create a calculated column in table

Flag = var a=FILTER('Table',[Serial Number]=EARLIER('Table'[Serial Number])&&[Status]="PASS")
var b=MINX(FILTER(a,[Test ID]=MAXX(a,[Test ID])),[Date])
return IF([Date]=b,1)

Then create a measure

Measure = SUM('Table'[Flag])

Put the measure to the visual

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TcT85]

Hi,

I have tried your suggestion and it's working with Direct Query.

It worked when i created the example in Excel.

Do you have a workarround for DirectQuery?

[v-xinruzhu-msft]

Hi @TcT85 

You can try to create a calculated table

Table 2 = ADDCOLUMNS(ALL('Table'),"Flag",var a=FILTER('Table',[Serial Number]=EARLIER('Table'[Serial Number])&&[Status]="PASS")
var b=MINX(FILTER(a,[Test ID]=MAXX(a,[Test ID])),[Date])
return IF([Date]=b,1))

Then create the measure

Measure = CALCULATE(SUM('Table 2'[Flag]),FILTER('Table 2',[Date] in VALUES('Table'[Date])&&[Product ] in VALUES('Table'[Product ])))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TcT85]

Hi Yolo Zhu,

I get this error message when i try to create a calculated table.

Error Message:
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

 I have to much data....

[v-xinruzhu-msft]

Hi @TcT85 

According to the error message, it can be seen that your data has exceeded the row limit, you can try the creation of calculated columns that I recommended at the beginning, and the Direct Query mode supports creating calculated columns

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TcT85]

Hi Yolo Zhu,

I get 2 error message with the first suggestion.

2nd row

1st row

[v-xinruzhu-msft]

Hi @TcT85 

Try the following code

Calculated column

Flag = var a=FILTER('Table',[Serial Number]=EARLIER('Table'[Serial Number])&&[Status]="PASS")
var b=CALCULATE(LASTNONBLANK('Table'[Test ID],0),a)
var c=CALCULATE(FIRSTNONBLANK('Table'[Date],0),a,'Table'[Test ID]=b)
return IF([Date]=c,1)

Then create the measure,  the measure is the same as I have offered before.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TcT85]

Hi Yolo Zhu,

Still getting error with DAX expressions on Direct Query.