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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Josef
Helper I
Helper I

Measure with replacing blank with 0 does not respect applied slicers/filers

Hi,

 

I'm working on a Power BI template for a wind farm company and I ran into some problems with a measure not respecting the applied slicers.

 

On my report I applied the next three slicers to all the report pages:

  • Year slicer
  • Month slicer
  • WindFarm

 

My end goal is to visalize the lost production catagorized as 'Environmental' per wind turbine and show 0 if the sum is blank. 

 

I first created a measure to calculate the total lost production:

 

Lost Production Total (kWh) SUM = SUM('Turbine Data'[Lost Production Total (kWh)])

 

 

Then I filtered on Category = Environmental

 

Environmental lost production (kWh) = [Lost Production Total (kWh) SUM] ('Turbine Data'[Category]="Environmental")

 

Until now the lost production per windturbine is shown correctly...

 

 

But when I try one of the below measures for displaying a 0 is when the value is blank, ALL wind turbines are shown, so the WindFarms slicer doensn't seem to be applied anymore:

 

Environmental lost production (kWh) + 0 = [Lost Production Total (kWh) SUM] ('Turbine Data'[Category]="Environmental") + 0
Environmental lost production (kWh) 0 if blank = IF(ISBLANK([Environmental lost production (kWh)]),0, [Environmental lost production (kWh)])

 


Does anybody know why this is happening? All help is appreciated! Thanks

10 REPLIES 10
az38
Community Champion
Community Champion

@Josef 

you've got a lot of syntax mistakes in your post

smth like

= CALCULATE(SUM('Turbine Data'[Lost Production Total (kWh)]), 'Turbine Data'[Category]="Environmental") + 0

should work

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 thanks for your feedback! I noticed some typos in the post, apologies for that. I tried your measure but the applied WindFarm slicer is still not respected with this measure, because all the wind turbines are displayed after adding this measure to the visualization. Any idea why this is happening?

 

az38
Community Champion
Community Champion

@Josef 

it's difficult to answer without data model. please, show an example


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 the simplified data model is like this, where the column 'Turbine' in the table turbine data is connected to the column 'Turbine' in the table turbines. I checked and made sure the names of the turbines are exactly the same in both tables, so that can't be the problem.  

DataModel.jpg

Hi @Josef ,

Can you please share a dummy pbix file and upload to OneDrive for Business? If you can't do that, please share some sample data and expected results. We will understand more clearly. 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xuding-msft,

 

Yes sure, I created a dummy pbix and uploaded it to OneDrive for Business.

You can download it here.

 

There are 2 pages in the file, one with the slicers and one with the sample visualization. 

The slicers are synced to the page with the visualization.

 

Also I created a screen recording gif to show what is the problem.

ScreenRecording.gif

 

Many thanks in advance for your help!

Hi @Josef ,

Sorry for late back. 

 

I test the dummy file. It is caused by the logic of DAX Query and the filter order.  For your scenario, I'm think you could try to remove Turbines[Turbine] and change it to Tubine Data [Turbine] in the matrix.

2.PNG

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xuding-msft many thanks for your reply!

Your solution is working perfectly, so thank a lot for that. 

 

However in my final scenario it's unfortunatly not possible to use the other column. This is because in my final scenario I have to display extra columns from the Turbine table. 

Do you perhaps know another solution, keeping the original column?

Even after watching the video 'Deep dive into DAX evaluation context' by SQLBI I still don't understand why PowerBI is igonoring the applied filters. 

Hope you can help 🙂

amitchandak
Super User
Super User

@Josef , I am hopeful this calculation is like this


Environmental lost production (kWh) =
calculate( [Lost Production Total (kWh)] ,'Turbine Data'[Category]="Environmental")

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak thanks for your feedback, but this measure is not displaying an 0 form the blank results. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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