Replace null value by 0 within a full outer join

[vbourbeau]

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

[Anonymous · Accepted solution]

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!

[Anonymous]

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]

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]

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

HTH,

Smitty