Forum Discussion

Tommyvhod's avatar
Tommyvhod
Helper II
3 years ago
Solved

Firstnonblank from different table

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's avatar
    Tommyvhod
    3 years ago

    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

     

     

     

2 Replies

  • Tommyvhod's avatar
    Tommyvhod
    Helper II
    3 years ago

    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's avatar
    v-yinliw-msft
    Community Support
    3 years ago

    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.