Replace blank rows with 0

[Brighton10]

Hello

I have two tables that have same columns. I have created 3 bridge tables on the country, region and sitename columns to have a one-to-many relationship. I then used the newly created country, region and sitename in my visual so i can use country in my slicer. The result in the attached pbix file (Test1BridgeTable) window is correct but i need blank values in the dieselFlowOver40l/hr to be replaced with 0. I have tried this measure but the visual is distorted whilst i just want to visualize data for filtered country.

DieselFlowOver40l/hr Count =

VAR max_Diesel_Flow_Over40 =CALCULATE(COUNTROWS(Table2),

Table2[alarmCode]="DieselFlowOver40",

FILTER(Table2,Table2[alarmStatus]="ON"),FILTER(Table2,Table2[dateTime]>=TODAY()-1)) +0

RETURN

max_Diesel_Flow_Over40

This measure is supposed to calculate the count of alarm status that were "ON" in the previous day. if there are none return 0 else return the count.

https://drive.google.com/drive/folders/1Fw3XtNsQD6EM9MbReNn6NuzLY8cs6KKH?usp=sharing 

Thanks

[v-yuaj-msft · Accepted solution]

Hi @Brighton10 ,

Based on your description, I modified @PhilipTreacy 's formula. Here it is as follows.

DieselFlowOver40l/hr Count = 

VAR countryname_ = SELECTEDVALUE(country[country])

VAR region_ = SELECTEDVALUE(region[region])

VAR sitename_ = SELECTEDVALUE(Table2[siteName])

VAR datetime_ = SELECTEDVALUE(Table2[dateTime])

VAR max_Diesel_Flow_Over40 =CALCULATE(COUNTROWS(Table2),

Table2[alarmCode]="DieselFlowOver40",

FILTER(Table2,Table2[alarmStatus]="ON"),FILTER(Table2,Table2[dateTime]>=DATEVALUE("2/20/2021")-1))

RETURN

IF(ISBLANK(max_Diesel_Flow_Over40)&&AND(NOT(ISBLANK([NavLightsStatus])),NOT(ISBLANK([Test_diesel_Level]))), 0 , max_Diesel_Flow_Over40)

Today is 2/23/2021, So I change the date from today() to DATEVALUE("2/20/2021").

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[Brighton10]

Thanks @v-yuaj-msft This works perfectly as expected. 

[PhilipTreacy]

Hi @Brighton10 

The modifiction I made in the file I linked to worked fine for me so I'm not understanding why it isn't working for you.

All you need is to change the line after RETURN to

IF(ISBLANK(max_Diesel_Flow_Over40), 0 , max_Diesel_Flow_Over40)

Is this what you have done?

Can you please link to the actual file you are getting the blanks displayed in so I can check it?

Regards

Phil

[Brighton10]

Hi @PhilipTreacy Thanks for response. I have done as you said, please see screeenshots attached. Please note the 2nd window visualization is using country, region and sitename from table2. The value for dieselLevel is the same for all countries which is the incorrect value. However if you look at the first window (Test1BridgeTable), before applying the modified measure it does filter and change according to country. The country, region and sitename used on this visual are from the bridge tables. When i modify the measure those values nolonger display and the diesleflowOver40l/hr becomes zero for every country. I hope the screenshots will give a clear picture of whats happening. The desired outcome should show zeros when blank at the same returning the correct value for dieselLevel and NavlightsStatus. Thanks

https://drive.google.com/drive/folders/1Fw3XtNsQD6EM9MbReNn6NuzLY8cs6KKH?usp=sharing 

Same values when modified appliedrelationshipsBefore modified measuredistorted visual after modified measureDesired ouput

[PhilipTreacy]

Hi @Brighton10 

Download your PBIX file with modified measure

Change the measure to this : I've added an IF after the RETURN

DieselFlowOver40l/hr Count = 
VAR countryname_ = SELECTEDVALUE(country[country])
VAR region_ = SELECTEDVALUE(region[region])
VAR sitename_ = SELECTEDVALUE(Table2[siteName])
VAR datetime_ = SELECTEDVALUE(Table2[dateTime])
VAR max_Diesel_Flow_Over40 =CALCULATE(COUNTROWS(Table2),
Table2[alarmCode]="DieselFlowOver40",
FILTER(Table2,Table2[alarmStatus]="ON"),FILTER(Table2,Table2[dateTime]>=TODAY()-1))
RETURN
IF(ISBLANK(max_Diesel_Flow_Over40), 0 , max_Diesel_Flow_Over40)

Regards

Phil

[Brighton10]

HI @PhilipTreacy , the diesel level is returning the same value instead of changing. The original pbix file on testbridge table is using values from the bridge tables specifically country,   region and sitename then the other table is using vales from the table. The desired table should have filtered diesel level values and zeros as shown. Thanks