DAX TREATAS column order

DarrylC — Wed, 22 Apr 2020 03:04:00 GMT

I have a report inherited from a previous colleague which includes a table created by a DAX statement. I have narrowed the problem down to the DAX table which is not returning any data when it looks like it should. I have checked the underlying tables (both in the database and also as they show in the PowerBI query section) and the raw data is coming through fine. I am very new to DAX but I believe it comes down to the ordering of the columns coming out of a NATURALLEFTOUTERJOIN which is then used as input into a TREATAS function. It is written in the following form:-

ResultTable =

VAR A = ALL ( SourceTab[KeyCol] )

VAR B = NATURALLEFTOUTERJOIN ( A, SourceTab2 )

VAR C = TREATAS ( B, Tab1[Col1], Tab2[Col2], .... )

RETURN C

Cutting back the DAX, if I remove the VAR C section and have it RETURN B, I get data returned. As soon as I add the VAR C section back it no longer returns data but does not error the DAX syntax. This tells me the right number of columns are defined in the TREATAS but that they might to be in the wrong order. Is this a reasonable assumption?

If so how can I either:

a) determine the order of the columns returned in VAR B so I can then map them correctly in VAR C

b) force the order of the columns returned in VAR B so I can map them correctly in VAR C

Any help would be greatly appreciated!

Re: DAX TREATAS column order

Anonymous — Wed, 22 Apr 2020 07:53:00 GMT

This code is not too great. Instead of using TREATAS, please use SELECTCOLUMNS. Also, instead of using LEFTOUTER..., you should create the right relationship between the tables.

Best
D

topic Re: DAX TREATAS column order in DAX Commands and Tips

DAX TREATAS column order

Re: DAX TREATAS column order