Title: Power BI - Slow DAX in Event Hub
Tags: azure;powerbi;dax;azure-eventhub
Question: I apologize for the for the vague question. I'm working with a data engineer on a project and I'm coming at this with limited knowledge of the backend.
We are using Azure Event Hub to stream data in Power BI. The data roughly follows this path, Azure Event Hub &gt; Streaming dataflow &gt; Dataset &gt; Power BI. The problem is that any DAX query run in Power BI (or directly in the model) has very bad performance.
It doesn't seem to be a problem with the DAX itself. The same DAX code run with an Azure Analysis Services connection on a similar number of rows runs fine. But when it's run with an Event Hub connection, the performance drops considerably (~3 sec vs &gt;1 min). Visuals loaded without DAX involved have pretty good performance actually, which makes me think that this issue has to do with the DAX interaction rather than the connection as a whole.
Has anyone experienced anything similar with an Event Hub connection in Power BI? Is there a way to address the DAX performance issue with this connection?



Here is another answer: Write optimized DAX expressions and design reports using below suggestions:
DAX Measures

DAX with Variables

SELECTEDVALUE () vs HASONEVALUE ()
A common scenario is to use HASONEVALUE() to check if there is only one value present in a column after applying slicers and filters, and then use the VALUES(column name) DAX function to get the single value.
SELECTEDVALUE () performs both the above steps internally and gets the value if there is only one distinct value present in that column, or returns blank in case there are multiple values available.
DISTINCT () vs VALUES ()
Power BI adds a blank value to the column in case it finds a referential integrity violation. For direct queries, Power BI by default adds a blank value to the columns as it does not have a way to check for violations.
DISTINCT (): does not return blank when encountering an integrity violation. It returns blank only if it is in part of the original data.
VALUES (): includes blank, as it’s added by Power BI due to referential integrity violations.
The usage of either of the functions should be the same throughout the whole report. Use VALUES () in the whole report if possible so that blank values are not an issue.
Ratio Calculation efficiently
Use (a-b)/b with variables instead of (a/b)-1. The performance is the same in both cases usually, but under edge cases when both a and b are blank values, the former will return blanks and filter out the date whereas the latter will return -1 and increase the query space.
Avoid IFERROR () and ISERROR ()
IFERROR () and ISERROR () are sometimes used in measure. These functions force the engine to perform a step by step execution of the row to check for errors. So wherever possible, replace with the in-built function for error checking.
Example: DIVIDE () and SELECTEDVALUE () perform an error check internally and return expected results.
For more, you can refer Power BI Performance Tuning Workflow.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:05/27/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<azure><powerbi><dax><azure-eventhub>', 'Title': 'Power BI - Slow DAX in Event Hub', 'ViewCount': 53, 'id': 72405643, 'user_AboutMe': '', 'user_AccountId': 23884061.0, 'user_DisplayName': 'Matt Kocak', 'user_Reputation': 676.0, 'user_Views': 37.0, 'user_id': 17884414.0}}