Latest Enterprise DNA Initiatives

Direct Query - Apply Functions to Columns

Hi All,

Currently, I am exploring the option of using Direct Query instead of the Import option to assess the performance of the model. I am querying a table with 20m rows. For simplicity, let’s assume that the data in the table is tidy and it’s in a good shape, so there is no need to apply transformation (I wish :slight_smile: ). I noticed when I try to create a measure (Using SWITCH function in my case) I couldn’t reference the column header? Any ideas as to why I can’t?

SWITCH( Expression(Column),Value1, Value2, …)

Thanks in Advanced.

Hello Hesham,

What I’ve found when I’ve had functions not working correctly is that I was trying to create the measure against the wrong table. Could that be what you’re doing here?



Hi @mickeydjw,

I agree with you, as I have fallen into that trap before. But what if I am querying only one table, I wouldn’t expect this to happen, right?

To bring this to life, I am attaching a sample PBIX file, which connects to AdventureWorksDW2014.DimProductCategory only. The table contains five columns, as seen below:

image .

View of the data in the ProductCategory table:

Let’s create a new Measure that use SWITCH to replace “Bikes” with “Bikes123”. As you can see, the column header doesn’t show:

**** Update ****
Interestingly, when I add a calculated column, the column headers show! This means, it will work with calculated columns only? But, I don’t wish to add more data, I would like to use measure.

Test - Direct Query - SWITCH.pbix (17.3 KB)

I’ve done some searching for more concrete answers and what I’ve found is that with SWITCH in a measure, you have to use the fully qualified column name (‘Table’ [Column]) and you also have to be using an aggregator (Min, Max, Sum).

Another option might be to edit the query to make the changes as you bring in the data?

Thanks mate.

Guess what … you give me a hint, and with a bit of trial and error, I managed to get it to work! WhooopWhooop :slight_smile:

First, I tried to use VALUES, TRUE, ALL but with no success. However, it worked with FIRSTNONBLANK. (Iterator + Row Context).

Great collaboration work @mickeydjw, appreciated.

Test - Direct Query - SWITCH.pbix (17.4 KB)

1 Like

Glad I could help