please help on NULL Calculation

[sivashankr]

Hi Team,

I have 2 tables 'Table A' and 'Table B'. I joined these 2 tables using M to 1 cardinality.
Now the requirement is I've calculation "Valid Customer ?:
if Table B.Customer is null then N
else Y

But I still see the blanks even though there is no customers.

PFA the .pbix

https://onedrive.live.com/?authkey=%21ABjErpmd%2DaLtnRQ&cid=C007331E59E3588D&id=C007331E59E3588D%211...

[v-xicai]

Hi @sivashankr ,

You may change the Cross filter direction of relationships among the tables above from Single to Both , which will take these tables treated as a single table. See more:Create and manage relationships in Power BI Desktop .

Best Regards,

Amy 

Community Support Team _ Amy

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

[sivashankr]

Hello @v-xicai 

I even tried with that option, but still no luck.

I posted the pbix file at blow path, could you please look at it and help me on this, If possible.

https://onedrive.live.com/?cid=C007331E59E3588D&id=C007331E59E3588D%21107&parId=root&o=OneUp

[BA_Pete]

Hi @sivashankr ,

The link to your PBIX doesn't work. Can you check the permissions are correct for sharing please?

Pete

[sivashankr]

@BA_Pete  please check this 

https://onedrive.live.com/?authkey=%21ABjErpmd%2DaLtnRQ&cid=C007331E59E3588D&id=C007331E59E3588D%211...

[ryan_mayu · Accepted solution]

@sivashankr 

There is no blank customer in table B and you are checking if there is any blank in table B

I think Table A has a bigger customer group than table B. So you need to create that column in table A

please try this

validcustomer = 
VAR CUS=LOOKUPVALUE('Table B'[CUSTOMER],'Table B'[CUSTOMER],'Table A'[Customer])
return if(ISBLANK(CUS),"No","Yes")

 

[sivashankr]

A Big Thank you @ryan_mayu , Its working as expected.

[ryan_mayu]

@sivashankr 

Have you tried isblank() or = blank() ?

[sivashankr]

Hello @ryan_mayu ,

I tried with both ISBLANK and BANK.

The problem what I see is the individual table does not have nulls. After I join the 2 tables and as a result of M-1 relationship I see nulls in Customer. Those are not either NULLS or BLANKS. As a result of this BLANK or ISBLANK or NULL, neither of them dont work.

[BA_Pete]

Hi @sivashankr ,

My first guess is that your data is not actually null, but blank.

If you look at the table in Power Query, do these cells specifically say "null", or are the cells just blank?

Pete

[sivashankr]

Hello @BA_Pete ,

The original table does not have nulls, I see the nulls after joing the 2 tables.

Thanks,

Siv

[BA_Pete]

Hi @sivashankr ,

How about switching your IF statement around, something like:

IF Customer <> null then "Y" else "N"

Pete

[sivashankr]

Still no luck @Pete