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 ). 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?
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?
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:
**** 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.
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?
). 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?
.
