Forum Discussion
please help on NULL Calculation
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
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")
12 Replies
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
Hello BA_Pete ,
The original table does not have nulls, I see the nulls after joing the 2 tables.
Thanks,
Siv
Hi sivashankr ,
How about switching your IF statement around, something like:
IF Customer <> null then "Y" else "N"
Pete
Have you tried isblank() or = blank() ?
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.
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.
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
Hi sivashankr ,
The link to your PBIX doesn't work. Can you check the permissions are correct for sharing please?
Pete
