topic Re: DAX Error: “Text with Number Comparison” When Filtering with Disconnected Slicer Using TREATAS in DAX Commands and Tips

DAX Error: “Text with Number Comparison” When Filtering with Disconnected Slicer Using TREATAS

user_guddu10 — Tue, 27 May 2025 02:59:22 GMT

Hi community,

I'm working on a Benchmark_Asset_Count_Rev measure that should dynamically filter a benchmark dataset (benchmarking_cleaned_combined) based on selections from two disconnected slicers:

SourceSelector[Source] — a text slicer with values like "Private Markets" and "Listed"
ScopeSelector[ScopeName] — a multi-select slicer for Scope 1, Scope 2, and Scope 3

Background

The dataset (benchmarking_cleaned_combined) contains rows of company emissions and revenue data. From that table, I created the following calculated columns in Power Query:

scope1_per_revenue = [scope1] / ([revenue_usd] / 1000000)
scope2_per_revenue = [scope2] / ([revenue_usd] / 1000000)
scope3_per_revenue = [scope3] / ([revenue_usd] / 1000000)
avg_emissions_per_revenue_mil = average of the 3, excluding nulls

These are used to calculate emissions intensity by scope.

Goal:
Filter benchmarking_cleaned_combined using:

TREATAS to apply the selected Source
Dynamic SWITCH(TRUE(), ...) to apply Scope-based emissions per revenue thresholds

Problem:
I keep getting this error:

"DAX comparison operations do not support comparing values of type Text with values of type Number."

Even though both:

benchmarking_cleaned_combined[Source] is a Text column
SourceSelector[Source] is also a Text column defined as a DATATABLE
Also scope columns and revenue columns are also decimal numbers

DAX Measure:

Benchmark_Asset_Count_Rev = VAR _SelectedScopes = VALUES(ScopeSelector[ScopeName]) VAR _IncludeScope1 = CONTAINS(_SelectedScopes, ScopeSelector[ScopeName], "Scope 1") VAR _IncludeScope2 = CONTAINS(_SelectedScopes, ScopeSelector[ScopeName], "Scope 2") VAR _IncludeScope3 = CONTAINS(_SelectedScopes, ScopeSelector[ScopeName], "Scope 3") VAR _SelectedSource = SELECTCOLUMNS( VALUES(SourceSelector), "Source", SourceSelector[Source] ) RETURN CALCULATE( DISTINCTCOUNT(benchmarking_cleaned_combined[company_name]), // Match disconnected source selector TREATAS(_SelectedSource, benchmarking_cleaned_combined[Source]), // Apply emissions filtering logic SWITCH( TRUE(), NOT _IncludeScope1 && NOT _IncludeScope2 && NOT _IncludeScope3, FALSE(), _IncludeScope1 && NOT _IncludeScope2 && NOT _IncludeScope3, benchmarking_cleaned_combined[scope1_per_revenue] > 0.1, NOT _IncludeScope1 && _IncludeScope2 && NOT _IncludeScope3, benchmarking_cleaned_combined[scope2_per_revenue] > 0.1, NOT _IncludeScope1 && NOT _IncludeScope2 && _IncludeScope3, benchmarking_cleaned_combined[scope3_per_revenue] > 0.1, benchmarking_cleaned_combined[avg_emissions_per_revenue_mil] > 0.1 ) )

Mock Data (benchmarking_cleaned_combined):

company_name report_year Source scope1 scope2 scope3 revenue_usd scope1_per_revenue scope2_per_revenue scope3_per_revenue avg_emissions_per_revenue_mil

A	2023	Private Markets	100	150	600	10000000	10.00	15.00	60.00	28.33
B	2023	Listed	0	0	50	50000000	0.00	0.00	1.00	1.00
C	2022	Private Markets	250	null	null	100000000	2.50	null	null	2.50

What I've tried:

Ensured both SourceSelector[Source] and [Source] in the main table are set to Text
Replaced IN with TREATAS
Used SELECTCOLUMNS to force a single-column table into TREATAS
Verified the slicer is not referencing any legacy column
Tried different ways to structure the logic (with FILTER, without SWITCH, etc.)

Would appreciate any help understanding why this comparison error keeps happening despite both columns being Text — and how best to combine TREATAS with scope logic inside CALCULATE.

Thank you!

Re: DAX Error: “Text with Number Comparison” When Filtering with Disconnected Slicer Using TREATAS

BA_Pete — Tue, 27 May 2025 06:21:00 GMT

Hi user_guddu10 ,

Firstly: I've given a thumbs-up for the effort you've spent laying out the background and problem so comprehensively, thank you. However, I still don't think there's enough information to 'properly' fix the issue.

In my experience, when you're getting into using TREATAS and complicated switching measures 99% of the time it's a modelling issue. Unfortunately this isn't likely something I'll be able to fully diagnose/resolve without significantly more information and a lot of time.

However, I would suggest the following based on what I can see:

1) Addressing the immediate issue: Make sure your column data types are set in Power Query as Text type, not just in the data model. Also check the relevant columns in Power Query for Error values.

2) Addressing the wider issue: I would recommend unpivoting your Scope1/2/3 columns in Power Query so the different scope values become attribute values in a single column. Remove the calculation of ScopeX_per_revenue from Power Query and do these calcs in your data model.

I think you'll find your whole measure/calculation experience a whole lot simpler and smoother when you can leverage implicit scope selections directly on the table (or via a relationship if you want to keep the ScopeSelector table related to the fact table).

Sorry I can't be of more immediate help, but I feel we're in XY Problem territory on this one with the actual issue being resolved via a model rebuild to some degree.

Pete

Re: DAX Error: “Text with Number Comparison” When Filtering with Disconnected Slicer Using TREATAS

user_guddu10 — Tue, 27 May 2025 06:30:49 GMT

Hi Pete, thanks for your input earlier!

I wanted to share that the issue has been resolved. The main challenge was caused by performance bottlenecks due to complex logic using SUMMARIZE + FILTER and row-level calculations in DAX. These were especially problematic when applying slicers across multiple related tables (e.g. sector, portfolio, client).

I resolved it by:

Precomputing avg_emissions_per_revenue_mil in Power Query, and
Rewriting the DAX using FILTER + DISTINCTCOUNT on the cleaned dataset instead of SUMMARIZE.
I also handled the scope and multi-select source logic directly inside the measure, using CONTAINS on VALUES(SourceSelector[Source]).

This dramatically improved performance and allowed scope/source slicers and client-linked filters to work smoothly together.

Thanks again for your help — I appreciate the engagement!