Do you have any nulls (or blanks) on any rows for the column LAK_NUM?
If you do, you might want to add an IF statement into your Variable to check for blanks and use a 0 value in its place. Something like
LAK_NUM = IF (ISBLANK([LAK_NUM]), 0, [LAK_NUM]
Without seeing your dataset that would be difficult to say. Generally i'd check things like:
From the data area of Power BI, you can copy the table and paste into Excel. I find that helps with the trouble shooting. Follow the data and it should start to come clear what is out of the ordinary.
I couldn't get this to work for me. When I created my expressions based on my data
Count [#"HAWB/HBL"] =
Var HAWB/HBL = [#"HAWB/HBL"]
'Your Table'[#"HAWB/HBL"] = 'HAWB/HBL')
The syntax error seems to pick on "var "hawb/hbl". I have tried without parethatsise as well and get the same issue.
Please can you help
@Ross73312 hi there, I just came across this post and it works exactly as suggested. thanks for that. My question is how I could use this or an equivalent in power query editor. I would prefer to add the calculated column in this environment because of subsequent logic I want to build into my table.
A find and replace of null might do what you are chasing.
Go to your "Replace Values" and write 'null' (without quotes) into the top box and then put 0 in the other box.
Is that the part of the post you wanted a Power Query version for?
hmm, no I don't think so. I'm looking for a query editor version to count the number of occurrences of a value of all rows in the table. It's this part of the post I am looking for an alternative for in query editor.
Count Emails = Var Emails = [Emails] RETURN CALCULATE( COUNTROWS('Your Table'), all('Your Table'), 'Your Table'[Emails] = Emails
If you are wanting to do a count, you could use the Grouping function. That will produce a count for you, but will summarize the table you use it in. If you don't want to upset your existing data, just create a new query to reference the old query and do the grouping in the new table. This table could be joined in the model.
The grouping function is found on the ribbon as "Group By". Tell it to group by the email address and use the "Count" function to create the summarised field.