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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

DAX SUM finds customers, that not exist

Hi, community members!

 

I have encountered a strange behaviour of the DAX aggregation. I have the following data set:

 

1. I have a table Branch_lookup:

BranchcountryCustomer TypeCity
US_NY_CORE1USCore1New York
US_NY_CORE2USCore2New York
US_NY_CORE3USCore3New York
US_WS_CORE1USCore1Washington
US_WS_CORE2USCore2Washington
US_WS_CORE3USCore3Washington
GE_BR_CORE1GermanyCore1Berlin
GE_BR_CORE2GermanyCore2Berlin
GE_BR_CORE3GermanyCore3Berlin

 

2. this is tied by Branch name to the cutstomer list:

Customer NameBranch
NM1US_WS_CORE2
NM2US_WS_CORE1
NM3US_WS_CORE1
NM4US_WS_CORE2
NM5US_NY_CORE2
NM6GE_BR_CORE1
NM7GE_BR_CORE2
NM8GE_BR_CORE3
NM9

GE_BR_CORE3

 

3. Customer Nmae is unique, the customer list is tied by Customer name to the sales:

 

CustomerValue
NM15000
NM210000
NM3300
NM5400

 

And then if I create a measure that aims to show 0 at the inactive customers:

 

ValueAll = IF(SUM(Sales[Value])=BLANK(),0,SUM(Sales[Value]))
 
It shows in the table customers with all branches:
 
DmitryVasilenko_0-1681121514164.png

 

 

3 REPLIES 3
BiNavPete
Resolver III
Resolver III

Trying replacing your formula with

ValueAll = IF(SUM(Sales[Value])=BLANK(),BLANK(),SUM(Sales[Value]))

 

Pete

Anonymous
Not applicable

Hi, this does not work, since it's simply the 

 

ValueAll = SUM(Sales[Value])

 

While I need to return "0"s in the field, where there were no sales, but only for valid customers.

Hi Dmitry

Is this what you're looking for as a result?

BiNavPete_0-1681222168901.png

It seems I can;t share a pbix on this forum which would be easier than a long explanation, but here goes.
In Powerquery merge the customers and branches into a single table with Branch as the Join.
Result below:

BiNavPete_1-1681222378243.png

Load this and the sales table to Power BI, create relationship on Customer name and your measure should work

 

Pete

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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