- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- No CALCULATE Challenge -- Round #2b

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

No CALCULATE Challenge -- Round #2b

10-11-2024
01:05 PM

@tamerj1 provided an ingenious solution to my No CALCULATE Challenge -- Round #2. However, I couldn't easily figure out how to adapt it to a situation where the measure is non-additive and the filtering I want to do is not a single value from a column.

In this case, I want to recreate this deceptively simple measure:

```
Distinct Orders (Non-China Benchmark) =
CALCULATE (
DISTINCTCOUNT ( Sales[OrderKey] ),
Geography[RegionCountryName] <> "China"
)
```

For motivation purposes, assume China is an outlier in my data that I want to ignore for benchmarking purposes.

Note that this probably isn't something I'd use on its own. It would likely be used as a denominator in another measure like this:

```
Distinct Order Ratio =
DIVIDE (
[Distinct Orders],
CALCULATE (
[Distinct Orders],
Geography[RegionCountryName] <> "China"
)
)
```

For full credit:

- CALCULATE and CALCULATETABLE are not allowed.
- The measure should work in any reasonable filter context, not just for the particular sample setup.
- Ideally, the measure should also be robust to model adjustments as discussed previously.

The primary goal is to find *any* solution.

The secondary goal is to find one that can be grokked by a non-expert.

Sample screenshot:

Sample PBIX file attached.

Prior participants: @SharmaAntriksh, @Greg_Deckler, @CNENFRNL, @ValtteriN

Other friends: @BA_Pete, @bcdobbs, @smpa01, @parry2k

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
05:32 PM

` inside a measure, `

Note that measures implicitly use CALCULATE, somewhat negating your premise.

Having said that, try this version

```
DONCB =
VAR b = ADDCOLUMNS(ALL(Geography[RegionCountryName]),"do", [Distinct Orders])
RETURN SUMX(FILTER(b,[RegionCountryName]<>"China"),[do])
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
10:18 PM

@AlexisOlson

Here is a solution using SUMMARIZECOLUMNS.

```
Distinct Orders (Non-China Benchmark) TJ =
COUNTROWS (
SUMMARIZECOLUMNS ( Sales[OrderKey],
FILTER (
ALL ( Geography[RegionCountryName] ),
Geography[RegionCountryName] <> "China"
),
"@Count", COUNTROWS ( Sales )
)
)
```

However, applying the same filter over a higher cardinality column will reveal the difference with the SUMX solution provided by @lbendlin . DISTINCTCOUNT is non-additive calculation, which means SUMX will result in wrong results.

24 REPLIES 24

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-13-2024
07:38 PM

@AlexisOlson

Here is a NOCALCULATE solution the way @Greg_Deckler preffers.

```
Distinct Orders (Non-China Benchmark) TJ3 =
COUNTROWS (
GROUPBY (
FILTER (
CROSSJOIN (
ALLSELECTED ( Sales[OrderKey] ),
FILTER (
ALL ( Geography[RegionCountryName] ),
'Geography'[RegionCountryName] <> "China"
)
),
NOT ISBLANK ( [Sales Amount] )
),
Sales[OrderKey]
)
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-13-2024
08:39 AM

Can you please explain further the SUMMARIZECOLUMNS bug that you have encountered. I've failed so far to reproduce it.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-13-2024
08:57 AM

@tamerj1, I see this when I define [DONCB] and add it to the matrix (nothing weird so far).

Then I clear the visual filter on RegionCountryName (still no bug).

Now when I select all but Armenia from that same visual filter, something breaks:

Because it's clearly a bug, it's possible it may depend on the specific version of Power BI you have installed and might not behave exactly the same way on your system.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-13-2024
08:34 AM

@AlexisOlson

In fact the following formula produces the exact same query plan that the CALCULATE does.

```
Distinct Orders (Non-China Benchmark) TJ2 =
SUMMARIZECOLUMNS (
FILTER (
ALL ( Geography[RegionCountryName] ),
Geography[RegionCountryName] <> "China"
),
"@Count", DISTINCTCOUNT ( Sales[OrderKey] )
)
```

The reason why the SUMX formula would work in this case is that there are no common Order Keys that could belong to different regions at the same time. For example, the first shape of your question that was posted in Linkedin was talking about "Color". The SUMX won't work in that case. It can also be simplified as:

```
SUMX (
FILTER (
ALL ( Geography[RegionCountryName] ),
Geography[RegionCountryName] <> "China"
),
[Distinct Orders]
)
```

But again that would work only if the each order is not related to more than one value of the filtered column. So that would work in the Store side of the data model wbut won't work in the 'Product' or the Customer side of the data model.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
10:18 PM

@AlexisOlson

Here is a solution using SUMMARIZECOLUMNS.

```
Distinct Orders (Non-China Benchmark) TJ =
COUNTROWS (
SUMMARIZECOLUMNS ( Sales[OrderKey],
FILTER (
ALL ( Geography[RegionCountryName] ),
Geography[RegionCountryName] <> "China"
),
"@Count", COUNTROWS ( Sales )
)
)
```

However, applying the same filter over a higher cardinality column will reveal the difference with the SUMX solution provided by @lbendlin . DISTINCTCOUNT is non-additive calculation, which means SUMX will result in wrong results.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-13-2024
08:31 AM

@tamerj1, yeah I realized that after I saw the SUMX. I accidentally chose a column where OrderKey has only one value per sale rather than a Product filter like I initially mentioned on LinkedIn.

SUMMARIZECOLUMNS does appear to be a viable more generic alternative to CALCULATETABLE and has a very similar syntax structure. If someone can understand SUMMARIZECOLUMNS, then CALCULATE should be no problem.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-13-2024
08:37 AM

@AlexisOlson

In fact I believe that CALCULATE/CLACULATETABLE table simulate the functionality of SUMMARIZECOLUMNS not the opposite.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
05:16 PM

@AlexisOlson I don't believe that the CALCULATE formula is returning the correct results. Here's why. If I add up all of the non-China distinct counts in Economy for Asia that are not China then I get 23,925. This would be a maximum number but the CALCULATE formula returns 28,189. There's no way to get that number really without adding in China's numbers to get you to 29,588 and then probably have some duplicates maybe? The point is though, I don't see how that number is possible quite frankly without ignoring the Filters pane filters for RegionCountryName which I'm not sure is or is not correct/intended.

Therefore, I would first like to ask that you prove that the 28,189 number is, in fact, correct and not something being made up by CALCULATE. Is it intended that it preserve all filters but ignore the filters in the Filters pane for RegionCountryName?

The solution would be the following for how the CALCULATE is working, which I feel isn't correct or at least extremely non-intuitive for the end user. I want to credit @lbendlin here as this measure is based off of his work.

```
DONC_D =
VAR __Continent = MAX( 'Geography'[ContinentName] )
VAR b =
SUMMARIZE(
FILTER(
ALL( Geography),
[ContinentName] = __Continent && [RegionCountryName] <> "China"
),
'Geography'[RegionCountryName],
"do", DISTINCTCOUNT( Sales[OrderKey] )
)
RETURN
SUMX( b,[do] )
```

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
08:25 PM

@Greg_Deckler, the distinct count for Asia Economy without any visual filter is 33,852. The distinct count for China Economy is 5,663. The difference is 28,189, exactly as intended. I really do want to keep the things filtered out in the visual filter in my real-life measure that motivated this contrived example.

Your measure is close but fails on the grand total line.

@lbendlin, I don't think even the most extreme No CALCULATE proponents would go so far as to ban measures altogether.

Your updated measure appears to work as intended so I've accepted it as a solution.

I realize now that I didn't think carefully enough when designing the problem. In my actual application, the measure isn't additive along any dimension, so DISTINCTCOUNT isn't nonadditive enough to solve what I'm really after.

This is a bit closer to what I'm trying to solve and might be more intuitive even though it's slightly more complex:

```
Median Markup =
MEDIANX ( Sales, -1 + Sales[SalesAmount] / Sales[TotalCost] )
```

```
Markup Non-Deluxe Ratio =
DIVIDE (
[Median Markup],
CALCULATE ( [Median Markup], 'Product'[ClassName] <> "Deluxe" )
)
```

Just like in my post, it should be unsurprising that the denominator is the same regardless of the ClassName selected via slicers or visual filters.

Consider this Round #2c. Ideally, the solution should be general enough that it works for any similar measure, not just the [Median Markup] example, just like it does with CALCULATE.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-12-2024
02:13 PM

@AlexisOlson Here is another version that is twice as fast and almost as fast as CALCULATE:

```
MRNC =
VAR __Stores = SUMMARIZE( 'Stores', [StoreKey] )
VAR __Products = SUMMARIZE( FILTER( ALL( 'Product' ), [ClassName] <> "Deluxe" ), [ProductKey] )
VAR __Table =
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
__Stores,
ALLSELECTED( 'Sales' )
),
__Products
)
VAR __Divisor = MEDIANX( __Table, -1 + [SalesAmount] / [TotalCost] )
VAR __Numerator = [Median Markup]
VAR __Result = DIVIDE( __Numerator, __Divisor )
RETURN
__Result
```

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-12-2024
08:26 AM

@AlexisOlson This solution appears to work when used in the same matrix as the 2b example. Including the total. Doubt it is the most efficient but seems to work.

```
Measure =
VAR __Stores = SUMMARIZE( 'Stores', [StoreKey] )
VAR __Products = SUMMARIZE( FILTER( ALL('Product'), [ClassName] <> "Deluxe" ), [ProductKey] )
VAR __Divisor = MEDIANX( FILTER( ALLSELECTED('Sales'), 'Sales'[StoreKey] IN __Stores && 'Sales'[ProductKey] IN __Products ), -1 + 'Sales'[SalesAmount] / 'Sales'[TotalCost] )
VAR __Numerator = [Median Markup]
VAR __Result = DIVIDE( [Median Markup], __Divisor )
RETURN
__Result
```

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-12-2024
07:58 AM

@AlexisOlson So is are these measures used in the same matrix visual or a different visual?

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-13-2024
08:42 AM

Here's a visual to check against (no slicer or visual filters applied). I've included my [Markup Non-Deluxe Ratio] measure along with both versions you've suggested so far.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-12-2024
07:34 AM

@AlexisOlson The fixing of the grand total is a very straight forward solve. Jusy yet another example of incorrect measure totals in Power BI. Honestly, Power BI gets the total wrong so often, I don't even pay attention to it any longer just assuming that it is wrong as I typically turn that garbage off anyway.

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
04:29 PM

Because SUMMARIZECOLUMNS doesn't (yet) seem reliable inside a measure, I'd prefer alternative approaches if they can be found. (It's only recently that this function doesn't throw an error when used like this.)

Also, SUMMARIZECOLUMNS is like CALCULATETABLE but arguably even more complicated, so IMO, it violates the spirit of the No CALCULATE ideology even though I didn't explicitly exclude it.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
03:00 PM

Hi, @AlexisOlson

I don't Know is My above question is Valid or not but you can see my Solution

```
Measure =
var a =
COUNTROWS(SUMMARIZECOLUMNS(
Sales[OrderKey],
FILTER(
VALUES(Geography[RegionCountryName]),
Geography[RegionCountryName] <> "China"
),"sf",COUNTROWS(Sales)
))
RETURN
a
```

Distinct Order Count Except **China**

```
Distinct Order (Non-China Benchmark) =
SUMX(
ALLSELECTED(Geography[RegionCountryName]),
[Measure]
)
```

Ratio

```
Distinct Order Ratio_ =
DIVIDE(
[Distinct Orders],
[Distinct Order (Non-China Benchmark)]
)
```

My Output

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
02:47 PM

Hi, @AlexisOlson

I have one question

Is the Same **OrderKey** present for Different **RegionCountryName**? Why Order key **29588** is the count for **Economy** class and **Asia**; if we do not take into account the order key for **China**, it becomes 29588-5663 = **23925**; nonetheless, in your situation, it displays **28189**.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
02:17 PM

```
DONCB =
VAR b = SUMMARIZECOLUMNS(
Geography[RegionCountryName],
FILTER(
ALL(Geography[RegionCountryName]),
[RegionCountryName] <> "China"
),
"do", [Distinct Orders]
)
RETURN
SUMX(
b,
[do]
)
```

Looking at the results they seem to be off though. China accounts for 5.663 distinct order keys but the value without China only goes down by 1399.

I think a more appropriate measure would be

```
DONCB =
var b = SUMMARIZECOLUMNS(Geography[RegionCountryName],FILTER(ALLSELECTED(Geography[RegionCountryName]),[RegionCountryName]<>"China"),"do",DISTINCTCOUNT(Sales[OrderKey]))
return sumx(b,[do])
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-11-2024
04:12 PM

@lbendlin, this seems to work sometimes but other times breaks for reasons I don't understand. Something seems buggy about SUMMARIZECOLUMNS inside a measure.

I can reliably reproduce the problem by opening the file, defining [DONCB], adding it to the matrix visual (by selecting the matrix and clicking the checkbox next to the measure name in the Data pane), clearing the RegionCountryName visual filter, and then selecting all but one country (say, Armenia) in that same visual filter.

Here's (a piece of) what it looks like when I follow exactly these steps (and no others):

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-12-2024
10:06 AM

`I can reliably reproduce the problem by opening the file, defining [DONCB], adding it to the matrix visual (by selecting the matrix and clicking the checkbox next to the measure name in the Data pane), clearing the RegionCountryName visual filter, and then selecting all but one country (say, Armenia) in that same visual filter.`

I find this rather alarming. This is something that @jeffrey_wang should be made aware of.

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors

User | Count |
---|---|

30 | |

16 | |

14 | |

14 | |

9 |