The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Trying replacing your formula with
ValueAll = IF(SUM(Sales[Value])=BLANK(),BLANK(),SUM(Sales[Value]))
Pete
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?
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
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
10 | |
7 |