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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hhammeren
Frequent Visitor

Measure Error-"couldn't load the data for this visual"

I have a card visual in my report that states the average time a customer takes to pay per year (when selected in the graph to the right).  I wanted to create a measure that does the following : when there is no average in the year 2017, take the average from 2016. 

Here is the original measure: Avg Days to Pay = AVERAGE('AR Accounts'[Pay Period (Days)]) 

And here is the measure I created from it to do what I wanted: Avg Days Pay Final = IF(CALCULATE([Avg Days to Pay], 'Date'[Year]="2017")=BLANK(), CALCULATE([Avg Days to Pay], 'Date'[Year]="2016"), [Avg Days to Pay])

 

I thought this would work but when I pulled it over to display on the card I got the error below. 

 

 

Avg Days to Pay.PNGAVG measure error.PNG

 

Is there another way I can get the result I want with a measure?

 

Thanks in advance. 

2 REPLIES 2
v-sihou-msft
Employee
Employee

@hhammeren

 

As the error message mentioned, we can't compare Integer values with Text values. In this scenario, your 'Date'[Year] column should be Integer, so you can't add quotes on 2017 and 2016 in your filter expression.

 

Avg Days Pay Final = IF(CALCULATE([Avg Days to Pay], 'Date'[Year]=2017)=BLANK(), CALCULATE([Avg Days to Pay], 'Date'[Year]=2016), [Avg Days to Pay])

Regards,

 

Thank you! This measure works when I change the Year Format to General instead of Whole Number.  

There is still one problem. Here is the measure : Avg Days Pay Final = IF(CALCULATE([Avg Days to Pay], 'Date'[Year]=2017)=BLANK(), CALCULATE([Avg Days to Pay], 'Date'[Year]=2016), [Avg Days to Pay])

 It says to only take the 2016 Avg Days to Pay data if the 2017 Avg Days to pay is BLANK, not 0. Yet, when i have avg Days to pay in 2017 that are 0, they are being replaced with the 2016 avg days to pay date. This is not what I want. I want it to show 0 in this case, as it should. 

 

Why is it recognizing BLANK() as 0 too?

 

Thanks again!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.