Forum Discussion

user_guddu10's avatar
user_guddu10
Advocate I
1 year ago
Solved

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

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:...
  • user_guddu10's avatar
    user_guddu10
    1 year ago

    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!

BA_Pete's avatar
BA_Pete
Super User
1 year ago

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

user_guddu10's avatar
user_guddu10
Advocate I
1 year ago

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!