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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AN3
Regular Visitor

Replacing Blanks with Zeros in a Matrix

Hi Everyone,

 

I have tried multiple measures and DAX formulas suggested online in this community and not one of them is working for me.

 

I am building a location plan by Headcount for my organisation and I have build a matrix table to show this data, however there are certain BSLs that have no headcount for a particular month in the particular site.  As I have conditional formatting set up based on a calculated field.  I need the blanks to show up as a Zero so the conditional formatting will work.

 

I have tried multiple different measures but when I add them into the table it just ends up duplicating the data with a bunch of zeros and throwing the order out of whack.

 

Sample Data:

GroupBSLStateSiteHC
AAaACTNAT1
AAaNSWALB1
AAaNSWNEW1
AAaNSWPAR1
AAaNSWPEN1
AAaNSWSYD1
AAaNSWWOL1
AAaQLDBRI1
AAaQLDTOW1
AAaQLDUMG1
AAaSAADE1
AAaTASHOB1
AAaVICBOX1
AAaVICDAN1
AAaVICMEL1
AAaVICMPO1
AAaWAPER1
AAbACTNAT1
AAbNSWALB1
AAbNSWGOS1
AAbNSWNEW1
AAbNSWPAR1
AAbNSWPEN1
AAbNSWSYD1
AAbNSWWOL1
AAbQLDBRI1
AAbQLDTOW1
AAbQLDUMG1
AAbSAADE1
AAbTASHOB1
AAbVICBOX1
AAbVICDAN1
AAbVICMEL1
AAbVICMPO1
AAbWAPER1
AAcACTNAT1
AAcNSWALB1
AAcNSWNEW1
AAcNSWPAR1
AAcNSWPEN1
AAcNSWSYD1
AAcNSWWOL1
AAcQLDBRI1
AAcQLDTOW1
AAcQLDUMG1
AAcSAADE1
AAcTASHOB1
AAcVICBOX1
AAcVICDAN1
AAcVICGEE1
AAcVICMEL1
AAcVICMPO1
AAcWAPER1
AAdACTNAT1
AAdNSWNEW1
AAdNSWPAR1
AAdNSWSYD1
AAdQLDBRI1
AAdQLDTOW1
AAdQLDUMG1
AAdSAADE1
AAdTASHOB1
AAdVICBOX1
AAdVICDAN1
AAdVICMEL1
AAdVICMPO1
AAdVICTRA1
AAdWAPER1
AAeACTNAT1
AAeNSWALB1
AAeNSWGOS1
AAeNSWNEW1
AAeNSWPAR1
AAeNSWPEN1
AAeNSWSYD1
AAeNSWWOL1
AAeQLDBRI1
AAeQLDTOW1
AAeQLDUMG1
AAeSAADE1
AAeTASHOB1
AAeVICBOX1
AAeVICDAN1
AAeVICGEE1
AAeVICMEL1
AAeVICMPO1
AAeWAPER1
AAfACTNAT1
AAfNSWALB1
AAfNSWNEW1
AAfNSWPAR1
AAfNSWSYD1
AAfNSWWOL1
AAfQLDBRI1
AAfQLDUMG1
AAfSAADE1
AAfTASHOB1
AAfVICBOX1
AAfVICDAN1
AAfVICMEL1
AAfVICMPO1
AAfWAPER1
AAgACTNAT1
AAgNSWALB1
AAgNSWGOS1
AAgNSWNEW1
AAgNSWPAR1
AAgNSWPEN1
AAgNSWSYD1
AAgNSWWOL1
AAgQLDBRI1
AAgQLDTOW1
AAgQLDUMG1
AAgSAADE1
AAgTASHOB1
AAgVICBOX1
AAgVICDAN1
AAgVICGEE1
AAgVICMEL1
AAgVICMPO1
AAgWAPER1
AAhACTNAT1
AAhNSWALB1
AAhNSWGOS1
AAhNSWNEW1
AAhNSWPAR1
AAhNSWPEN1
AAhNSWSYD1
AAhNSWWOL1
AAhQLDBRI1
AAhQLDTOW1
AAhQLDUMG1
AAhSAADE1
AAhTASBUR1
AAhTASHOB1
AAhVICBOX1
AAhVICDAN1
AAhVICGEE1
AAhVICMEL1
AAhVICMPO1
AAhWAPER1

 

AN3_0-1679967107179.png

The picture above highlights the yellow spaces that are currently blank, I want these to show up in Power BI as Zero.

 

Measure that I have tried include:

Headcount (Blank replaced) = IF(ISBLANK(SUMX(SSOT,[Headcount])), 0 , SUMX(SSOT,[Headcount]))
All Blank Rows =
[Head Count] = BLANK()
Replace HC = IF([All Blank Rows], BLANK(), COALESCE([Head Count],0))
Headcount = sum(Table[Headcount])+0
Headcount = IF('Table'[Col_1]=BLANK(),0,'Table'[Col_1])
 
With all these measures all I get is this:
 
 AN3_3-1679967924723.png

 

My filters on this matrix include - Group, Site and Date


My relationships behind the tables are like this:

AN3_2-1679967706105.png

 

 If anyone could help, I would really appreciate it.
 
Thank you,
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @AN3 

 

You can try the following methods.
New Table

Table 2 = SUMMARIZE('Table','Table'[Group],'Table'[BSL])

vzhangti_0-1680155876569.png

Measure = SUM('Table'[HC])+0

vzhangti_1-1680155943947.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

 

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @AN3 

 

You can try the following methods.
New Table

Table 2 = SUMMARIZE('Table','Table'[Group],'Table'[BSL])

vzhangti_0-1680155876569.png

Measure = SUM('Table'[HC])+0

vzhangti_1-1680155943947.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

 

Hi,

Thanks for your help, this solution worked, except that I had to summarize both the Group and BSL as a Table and the Site and the State as a separate Table, deleting duplicates that were affecting the relationship.  Once I connected both these tables up, I could then use the calculation measure and it worked.  

 

Thank you very much for your help 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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