Forum Discussion

vbourbeau's avatar
vbourbeau
Resolver II
6 years ago
Solved

Replace null value by 0 within a full outer join

Hi

 

look the below image...

I want to get the future stock value. For that I have to add "stock" to future "transaction", it's giving me the balance.

All work great but It's kind of full outer join. I have "stock" without "transaction" and "transaction" without stock. also here it's ok. But how can I replace the blank value with 0???

 

see the source here https://we.tl/t-Tx1aRpTfmY

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi vbourbeau ,

     

    Create 3 measures as below:

     

    _In = IF(MAX('Stock'[Part]) in FILTERS(Part[Part])||MAX('Transaction'[Part]) in FILTERS(Part[Part]),MAX('Transaction'[In])+0,BLANK())
    _Out = IF(MAX('Stock'[Part]) in FILTERS(Part[Part])||MAX('Transaction'[Part]) in FILTERS(Part[Part]),MAX('Transaction'[out])+0,BLANK())
    _Qte = IF(MAX('Stock'[Part]) in FILTERS(Part[Part])||MAX('Transaction'[Part]) in FILTERS(Part[Part]),MAX('Stock'[Qte])+0,BLANK())

     

    And you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Create new measures with + 0

    _Qte = SUM(Stock[Qte]) + 0
    _In = SUM(Trans[In]) + 0
    _Out = SUM(Trans[Out]) + 0
    You will probably get additional rows, but there will be zeroes!
    HTH,
    Smitty
    • vbourbeau's avatar
      vbourbeau
      Resolver II
      6 years ago

      yeah I try something like that but I get all the part. In my real life database I have lot of part I don't want to see it there.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Add a Filter to the visual, on Balance > 0 to reduce the extra rows.

        HTH,

        Smitty 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi vbourbeau ,

     

    Create 3 measures as below:

     

    _In = IF(MAX('Stock'[Part]) in FILTERS(Part[Part])||MAX('Transaction'[Part]) in FILTERS(Part[Part]),MAX('Transaction'[In])+0,BLANK())
    _Out = IF(MAX('Stock'[Part]) in FILTERS(Part[Part])||MAX('Transaction'[Part]) in FILTERS(Part[Part]),MAX('Transaction'[out])+0,BLANK())
    _Qte = IF(MAX('Stock'[Part]) in FILTERS(Part[Part])||MAX('Transaction'[Part]) in FILTERS(Part[Part]),MAX('Stock'[Qte])+0,BLANK())

     

    And you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!