topic Re: DAX SUM finds customers, that not exist in DAX Commands and Tips

DAX SUM finds customers, that not exist

Anonymous — Mon, 10 Apr 2023 10:14:03 GMT

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:

Branch	country	Customer Type	City
US_NY_CORE1	US	Core1	New York
US_NY_CORE2	US	Core2	New York
US_NY_CORE3	US	Core3	New York
US_WS_CORE1	US	Core1	Washington
US_WS_CORE2	US	Core2	Washington
US_WS_CORE3	US	Core3	Washington
GE_BR_CORE1	Germany	Core1	Berlin
GE_BR_CORE2	Germany	Core2	Berlin
GE_BR_CORE3	Germany	Core3	Berlin

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

Customer Name	Branch
NM1	US_WS_CORE2
NM2	US_WS_CORE1
NM3	US_WS_CORE1
NM4	US_WS_CORE2
NM5	US_NY_CORE2
NM6	GE_BR_CORE1
NM7	GE_BR_CORE2
NM8	GE_BR_CORE3
NM9	GE_BR_CORE3

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

Customer	Value
NM1	5000
NM2	10000
NM3	300
NM5	400

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:

Re: DAX SUM finds customers, that not exist

BiNavPete — Mon, 10 Apr 2023 12:24:25 GMT

Trying replacing your formula with

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

Pete

Re: DAX SUM finds customers, that not exist

Anonymous — Mon, 10 Apr 2023 12:40:47 GMT

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.

Re: DAX SUM finds customers, that not exist

BiNavPete — Tue, 11 Apr 2023 14:14:25 GMT

Hi Dmitry

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

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:

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

Pete