Forum Discussion

DarrylC's avatar
DarrylC
New Member
6 years ago

DAX TREATAS column order

Hi

 

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

OR

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!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    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