Ben has attended:
Power BI Modelling, Visualisation and Publishing course
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
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
