I'm not sure where to ask this, but this has always seemed like an active community, so I'd thought I'd take a chance. Does anyone know if there is a way to make suggestions about the DAX language itself? I am trying to create a conditional that returns a table and it seems like this cannot be done with the language as is. If anyone has information as to where I can direct this suggestion, I would appreciate it.
@vega it is correct forum to ask DAX question, and now related to your question, it all depends on use case. There are many DAX functions which can create tables:
and many more...
Right, my problem isn't creating tables. My problem is returning tables from a conditional. For example:
IF( <Condition>, TableA, TableB )
SWITCH( <Condition>, <Value>,TableA, <Value>,TableB )
Both of these cases are not possible with the language as is. This is why I am looking for a location to submit suggestions for the language.
@vega you can do what you are asking for but I'm not fully clear what you are planning to do after you had table returned based on condition. I think if you explain the full use case it will help to understand what you are trying to solve.
Well, I had read the documentation of some functions:
The main takeaway was the following for result: "Any scalar expression to be evaluated if the results of expression match the corresponding value."
The issue that I am having is not with Power BI directly. It is with Azure Analysis Services that I am connecting to via Power BI. I am trying to make use of the Detail Rows Expression which takes a DAX expression that returns a table and returns this table anytime a user drills down. Making use of a conditional does not work because the error is returned:
"Calculation error in measure <MeasureName>: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
My formula is simple, it is just an if statement that returns one table or another base on a condition. I believe this error is being returned because it is expecting a scalar to be returned (as per the documentation) and a table is being returned.
IF( SELECTEDVALUE(TimeIntelligence[Abbreviation]) = "YTD", CALCULATETABLE(JournalEntries,'JournalEntries'[isYTD] = TRUE()), CALCULATETABLE(JournalEntries,'JournalEntries'[isMTD] = TRUE()), )
I have ran into this article. The problem is that this article does not mention conditionals. My original DAX formula was as follows:
IF( SELECTEDVALUE(TimeIntelligence[Abbreviation]) = "YTD", SELECTCOLUMNS( CALCULATETABLE(JournalEntries, JournalEntries[isYTD] = TRUE()), "Hotel Name", MAXX(RELATEDTABLE(Hotels), Hotels[HotelName]), "Hotel Account Number", JournalEntries[HotelAccount], "GL Number", JournalEntries[GLAccountNumber], "Date", JournalEntries[DateKey], "Batch Number", JournalEntries[BatchNumber], "Line Number", JournalEntries[LineNumber], "Comment", JournalEntries[Comment], "Amount", JournalEntries[Amount] ), SELECTCOLUMNS( CALCULATETABLE(JournalEntries, JournalEntries[isMTD] = TRUE()), "Hotel Name", MAXX(RELATEDTABLE(Hotels), Hotels[HotelName]), "Hotel Account Number", JournalEntries[HotelAccount], "GL Number", JournalEntries[GLAccountNumber], "Date", JournalEntries[DateKey], "Batch Number", JournalEntries[BatchNumber], "Line Number", JournalEntries[LineNumber], "Comment", JournalEntries[Comment], "Amount", JournalEntries[Amount] ) )
Which follows more of the format of what the article provides, but still produces the same error.
@vega it will not work as error suggested. You cannot return table in dax formula but you can use this table to filter etc but DAX has to return scalar value. Although you can create new table using DAX
@parry2k, I am aware of this. My original question was to find a location to suggest improvements for DAX. I am aware that this cannot be done, hence I was not looking for a solution, I was looking for a contact to the whoever is responsible for the DAX language.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!