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!
