Firstnonblank from different table

[Tommyvhod]

Hello all

I have 2 tables, in one the production steps of a part and in second the rejected parts IDs.

I would like to add the reject reason code to the rejected table from the production table .

In the production table one ID appears with blank value (reject reason empty) until one item is rejected, than a reject reason code is added.

I used:

Reject reason = CALCULATE(

        FIRSTNONBLANK( Data[Rjct code], TRUE()),

        FILTER( Data, Data[ID] = Scrap[ID]))

On some of the rows the correct answer appeared, but some of them is left blank ( where data exists in the data table)

What could be the reason?

Thank you

[Tommyvhod · Accepted solution]

SO i was able to solve the issue. I assumed that the FIRSTNOBLANK finds the first no blank values between the filtered data, which seems to be incorrect.

I added another filter with && filtering the process step, there additional results appeared.

I added a third filter for a third row, reject qty = 1. with these I have correct results.

The final formula looks like:

reject code = CALCULATE(FIRSTNONBLANK(Data[Rjct code], TRUE()),
FILTER(Data, Data[ID] = Scrap[ID] && Data[Operacia] = Scrap[OP] && Data[Rejct Qty] = 1))

Maybe there is a more elegant way to do this... long way to learn DAX

Thx

[v-yinliw-msft]

Hi @Tommyvhod ,

1.Based on this"In the production table one ID appears with blank value (reject reason empty) until one item is rejected, than a reject reason code is added."

Please just build a  relationship between two tables:

2. I would like to add the reject reason code to the rejected table from the production table .
Please try this method to create a column:

Column = LOOKUPVALUE(Data[Rjct code],Data[ID],'Scrap'[ID])

Here is my PBIX file. Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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