cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Microsoft
Microsoft

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors