Can't create measurements in DirectQuery?

[Anonymous]

I've got a directquery instead of a table loaded in. When I try to create measurements it won't let me do it, but I've seen online examples where it works. 

If I try to referece (Query1[insert random column]) nothing picks up at all, it picks no columns up in Query1 even though I have hundreds in it. Does anyone know why this is? 

[HarishKM]

@Anonymous  Kindly refer this documentation while working on direct query .

few function is not working in DQ at power bi . I will suggest do all the calculation in database itself for more convenient. it will not put extra load as well .

[StefanoGrimaldi]

depending in the contest you are using the dax it wont appear unless under a condition its a validated use, you doing the dax for a new column? new table? measure? etc and what you trying to aachive, if you want a faster and better response you would need to give more details. 

[Anonymous]

@StefanoGrimaldi Sorry it's a DAX measure I want to create, I want to create a few but they aren't going through at all. 

This is the error I'm getting below. 

"A single value for column 'Second UPN' in table 'Query1' cannot be determined. This can happen when a emasure formulae refers to a column that contains many values without specifiying an aggregation such as min, max, count, or sum to get a single result."

The measures that I want to make don't include min, max, count etc. 

[StefanoGrimaldi]

you will need to send more details as your measure complete dax and some dummy data of the tables to be able to determine your error over the formula

[Anonymous]

@StefanoGrimaldi 

Okay well it's a bit difficult to show since I do have a lot of data here. But I'll show you what I'm using as it's a Power Bi connector. I'm using a Microsoft Teams integration that provides all the data from Microsoft Teams into Power Bi. However, it's only accessible in DirectQuery, most likely due to how big it is. 

https://docs.microsoft.com/en-us/microsoftteams/cqd-power-bi-query-templates

But I can't manipulate any fields. For example here is just one of the many errors below, this is when I create a new column in DAX so I can change true/false text values into another value (Inbound/Outbound) calls.

When I apply this to a table visual it goes blank and has this error

"OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.."

When I'm using a loaded table I can do all these sorts of functions with no problems. In DirectQuery nothing seems to work when I try to manipulate the data. 

Here's another when I try to count the rows, because I want to make a count for the true/false values, how many are there in a column. The column isn't recognised and can't be referenced.

And once again when I try to change the name of a user in a specific column, it doesn't recognise the column. (Took the names out for privacy reasons)

This is why I'm not being very specific because there's a whole boat of issues that I'm finding with it. I'm not too sure why though, I can't find much learning material on what's different with DirectQuery and what DAX functions you can apply and not apply. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

First error, you will need add a measure name before the formula.

Second error, you can't directly use columns in measure, you will need use SELECTEDVALUE() function to get the column.

For example:

IF(SELECTEDVALUE('table'[column]="value",1,0))

Best Regards,

Jay