Syntax issues

[the75th]

Hey guys, I've been having some issues with my current project and I've been basically grinded to a halt.

I must admit I'm a beginner to tabular models, PowerBI and dax. I generally don't have an issue with multidimensional.

What I'm trying to do is pretty simple. The company I'm at cleanses input data from several sources and basically has 1 central golden record. All this information has been given to me and I simply have to make a few KPI's like how many records were cleansed etc.

The first issue I have is that I can't import the database and have to work with DirectQuery. I've been getting errors all day when trying to do pretty simple things.

My first KPI should look like this:

Calculated column1, let's call it CompleteName = if(isblank(RawData[firstname]);

RawData[lastname]; some sources input the full name of the customer into 1 record, being lastname

RawData[lastname] & " " & RawData[lastname] )

Calculated column2 ( = match) Exact(RawData[CompleteName] ; Golden[CompleteName])

Measure 1 (Matches) sum(if(RawData[match] ; 1 ; 0))

After this I'll basically create a new measure that divides 'matches' with the total amount of records I have.

My issue is that I can't even do the first concatenate, I keep getting the same syntax error, it fails at line 63. I intially thought that was the case because it couldn't concatenate blanks or NULL values so I added the isblank into it. (Is this true by the way?)

It didn't really solve anything. I tried all of the queries with mock data in other tables and somehow they all seemed to work fine there. If anyone could help me figure out what I'm doing wrong with the first DAX query I'm confident I'll be able to sort all of the other ones out myself.

Also, I can't seem to add calculated columns directly into PowerBI, somehow I have to do this through Microsoft Azure Analysis Services Web designer since I'm working on a live connection is that normal?

Sorry for the many questions in one single post.

[the75th]

Hey thanks for the response. I can't really provide you with a sample or pictures as it's a database full of private (and company) information.

The structure is pretty simple, 1 database with 3 tables. Each contain similar data (name, e-mail adresses, telephone numbers, etc...) and I simply need to compare the data from table 1 (pre cleanse data) to table 3 (golden records). Business has written out their KPI's and I just need to write queries and visualize them.

Another thing that might not be entirely clear is that I'm connected to SSAS and not to the database directly. I can add new calculated columns and write queries on Microsoft Azure Analysis Services Web designer - preview but not on PowerBI desktop. That isn't really the problem, the main issue is the fact that ANY query I write gives me the same error being "
The query referenced calculated column xxx which does not hold any data because there is an error in its expression."

Now, the funny thing is that I tested all of my queries with mock data and they worked fine there. Furthermore, I tried basic queries of which I know would never fail and they all don't work when loaded into PBI. 

I've come to wonder whether or not the issue is actually with the connection/database and not syntax or DAX related?

[v-danhe-msft]

Hi @the75th,

For the firest issue, I could not reproduce your problem, could you please post me more pictures about your data structure or sample data and your desired result?

For the second issue, if you are suing the "Live connection" mode in Power BI Desktop, there is some limitations that you could not create a calculated column. Reference:https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#live-connections

Regards,

Daniel He