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
annetoal
Helper II
Helper II

Limitations on kinds of data DAX can use?

I'm working with a streaming dataset from Power Automate. It is a Flow that lets the user request sign-off on files in my Office 365 OneDrive. I made a streaming dataset in PowerBI and set it to collect the date the request was submitted and the date it was approved. Now I want to have a measure in PowerBI that tells me the time elapsed between date submitted and date approved. 

ElapsedHours = DATEDIFF('RealTimeData'[Date approved],'RealTimeData'[Date submitted],MINUTE)/60

When ElapsedHours is put into a PowerBI report, I get "Couldn't load data for this visual" error.

When I am writing the measure, PowerBI offers to auto-complete things like 'RealTimeData'[...]

But when I put the name DateSubmitted or DateApproved inside the square brackets, PowerBI puts a red squiggly line under those names. Suggests that PowerBI doesn't recognize the data.

 

This is date-time data from Flow. When I make a table in a PowerBI report, it is able to display the data. The data is in there, PowerBI can see it, but there's something about it that is making it impossible for DAX to use to calculate a DATEDIFF. Please help!

 

report-view.png

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

A measure requires aggregation of some kind.  You could try writing it as a column to keep it simple.

View solution in original post

4 REPLIES 4
HotChilli
Super User
Super User

A measure requires aggregation of some kind.  You could try writing it as a column to keep it simple.

Thanks for the tip. I added MAXA before the variable name and it worked.

Elapsed minutes = 
DATEDIFF(MAXA('RealTimeData'[DateSubmitted]), MAXA('RealTimeData'[DateApproved]), MINUTE)

Appreciate the help!

Anne 

With a streaming dataset, I don't have the ability to add extra columns to RealTimeData. I have to use a measure to calculate the difference between the two times. So is there some limitation on what DAX can use? Is there some workaround? I tried creating a column by making a measure 

 

Submitted = 'RealTimeData'[DateSubmitted]

 

But this also resulted in an error message when I tried to put Submitted into a PowerBI report. Any ideas? 

The measure has to use an aggregation but datediff has to use row context.

Probably using MAX round the column name should work.  As long as the table visual is the same as you posted.

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.

Top Solution Authors