Create a Measure from Direct Query Connection

[STIBBS_807]

I am new to Direct Query on data. I would like to create a measure for an item within a folder of the table.

Table format:

Case Table

    Item One

    Item Two

    Folder One

        Folder One Item One

        Folder One Item Two

The measure that I am trying to create.

ItemLen = len('Case Table'[Folder One Item Two])

This does not work. I tried this next.

ItemLen = len('Case Table''Folder One'[Folder One Item Two])

This does not work.  

ItemLen = len('Case Table','Folder One',[Folder One Item Two])

This does not work.  

ItemLen = len('Case Table'.'Folder One'.[Folder One Item Two])

This does not work.  

I need help with writing this measure.  Thank you.

[STIBBS_807]

Still could not get this to work.  So lets go to something simpler.  I have a direct query to a cloud based database.  In the one table I have a start date.  I am trying to figure out how many days has elapsed between the start date and today's date.

Here is my calculation:

DaysOpen = CALCULATE(DATEDIFF(MAX(d_case[ADJUSTED_CASE_START_DATE]),today(),day))

This appears to calculate the days correctly.  

But, I would like the Average Days for the Case.  Just one number for all the DaysOpen. So I did this:

AvgDaysOpen = calculate(AVERAGE((DATEDIFF(MAX(d_case[ADJUSTED_CASE_START_DATE]),today(),day))))

I get this error:

The AVERAGE function only accepts a column reference as an argument.

So I tried to use the measure that I created

AvgDaysOpen = calculate(AVERAGE([DaysOpen]))

This also gives me an error.

The AVERAGE function only accepts a column reference as the argument number 1.

Do I have to create some kind of array to average the result from the DaysOpen?

[Pragati11]

Hi @STIBBS_807 ,

I don't understand th format of your tables. Can you elaborate more?

What do you mean by "measure for an item within a folder of the table"?

[STIBBS_807]

Here is the table and the folder I am trying to get to:

I want to get the length of the Case Owner Team item to figure out where to truncate the first four characters off the word.

So my formula is: right(Case Owner Team, (len(Case Owner Team)-4))

[STIBBS_807]

Here is the Data folder in Power BI.  See the folder in the list.

[Pragati11 · Accepted solution]

HI @STIBBS_807 ,

Folder is just a display view within your CASES table.

I think the problem here is the DAX function that you are using.

LEN function returns the length of a text value. Here you are trying to apply it directly on a column which is wrong. This LEN function works on a single text value.

You can read more details about this function here:

https://learn.microsoft.com/en-us/dax/len-function-dax

If you want to use this function on a column, you will have to use an agrregation on your column before applying LEN dax function to it.

Something like this below:

test = LEN(MAX('Countries emissions'[CO2 Emission Type]))

Hope this helps.