Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vega
Resolver III
Resolver III

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
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

hope this post is helpful.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.