cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vega Member
Member

DAX Suggestions

Hello,

 

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. 

9 REPLIES 9
Super User
Super User

Re: DAX Suggestions

@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:

 

- Calculatetable

- Summarize

- GroupBy

- SummarizeColumns

- Filter

- Values

 

and many more...






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





vega Member
Member

Re: DAX Suggestions

Right, my problem isn't creating tables. My problem is returning tables from a conditional. For example:

 

IF(
    <Condition>,
    TableA,
    TableB
)

OR

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.

Super User
Super User

Re: DAX Suggestions

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





vega Member
Member

Re: DAX Suggestions

Well, I had read the documentation of some functions:

https://docs.microsoft.com/en-us/dax/switch-function-dax

https://docs.microsoft.com/en-us/dax/if-function-dax

 

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()),
)
Super User
Super User

Re: DAX Suggestions

@vega yes it is not expected to work as error suggested. For AAS drill thru there is different solution.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

Re: DAX Suggestions

hope this post is helpful.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





vega Member
Member

Re: DAX Suggestions

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.

Super User
Super User

Re: DAX Suggestions

@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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Highlighted
vega Member
Member

Re: DAX Suggestions

@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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 58 members 1,043 guests
Please welcome our newest community members: