dax measure column filter

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » DAX measure column filter

DAX measure column filter

resolvedResolved · High Priority · Version 365

DAX measure column filter

Hi,

I have several related tables, and am trying to use the DAX measure calculation to multiply a column from one table, by a column from another table. I want one of these column values to be dynamic dependent on the filter on the page.

e.g.

Options filter x, y, z

DAX expression: column A (from Table 1, dependent on the page's filter using related Table 2) * column B (from related Table 3).

Table's 2 & 3 have a one to many relationship with Table 1.

The objective is to have different outputs depending on a scenario, the "filter" chosen.

My first question is if this is possible using DAX, and if so which functions do I need to use to do this? Or, is there another, better way of achieving what I'm trying to do?

If anything's unclear happy to provide more detail.

Many Thanks,
Ben

RE: DAX measure column filter

Hi Ben,

Thank you for the forum question.

To multiply one column from one table with a column from another table just write a new measure.
Name of measure=sum(tablename[column Name])*sum(tablename[column name]).

When you add the measure to a visual the measure will be filtered by any filters and grouped by what you add to the visual.

Let me know if my suggestion is not doing what you expect.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Edited on Wed 17 Apr 2024, 12:04

RE: DAX measure column filter

Hi Jens,

Thank you for your quick response.

I'm still having an issue. I think the problem is the measure isn't changing based on how the table it's related to is filtered.

What I want to do in a way, is filter a table (table 1 below), so it would change from having a "many to many relationship" to a "one-to-many" relationship with another table.

Filter options (this represents different scenarios):
x,
y

Table 1:

Entity, Variable Option, Cat, Value
EUI, x, a, 10
EUI, x, b, 20
EUI, y, a, 5
EUI, y, b, 15

Table 2:

Ref, Cat, Measure column (result based on filter)
Unique_info_1, a,
Unique_info_2, b,
Unique_info_3, a,
Unique_info_4, b,

I want it so:

e.g.
If the filter chosen is x, table 3 looks like:

Ref, Cat, Measure column
Unique_info_1, a, 10
Unique_info_2, b, 20
Unique_info_3, a, 10
Unique_info_4, b, 20

If the filter chosen is y, table 3 looks like:

Ref, Cat, Measure column
Unique_info_1, a, 5
Unique_info_2, b, 15
Unique_info_3, a, 5
Unique_info_4, b, 15


Is there a simple way to do this using DAX with the format tables 1 & 2 are in?

Thanks for your help.

Ben

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Optimize Report Performance

Pay attention to report performance by optimizing your data model and report design. Use the Performance Analyzer tool to identify bottlenecks in your report and improve loading times. Techniques such as using summarisation, avoiding unnecessary visuals, and optimizing DAX queries contribute to a faster and more responsive report.

View all Power BI hints and tips


Server loaded in 0.07 secs.