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

[Josef]

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

[az38]

@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

[Josef]

@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]

@Josef 

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

[Josef]

@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.  

[v-xuding-msft]

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. 

[Josef]

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.

Many thanks in advance for your help!

[v-xuding-msft]

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.

[Josef]

@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]

@Josef , I am hopeful this calculation is like this

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

[Josef]

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