Calculated Column Based on Unrelated, Filtered Table

[Anonymous]

Currently, I have 2 tables : SalesTable and FilterTable. SalesTable is my main data table with 20+ fields, which includes columns called "Price with Tax" and "Price without Tax". FilterTable only contains one column and has 2 rows: "Price with Tax" and "Price without Tax". My goal is to have a filter with the values from FilterTable, and when the user selects one of the values, I'll have a created DAX column called "Final Price" to take that filter value and update the DAX calculation accordingly. Right now, I have the filter set up and the DAX forumula below to create my new FinalPrice column - when the user changes the filter value, this calculation changes as well.

This is working perfectly, however, it's set up as a measure and not a standard column (and I need this to be evaluated at the row level). When I try to convert this to a column, the dynamic filtering does not work. I select different filters but the calculation does not change. Is there an easy way to update the below code to get this workable for a column? I've tried removing the MAX() pieces and tweaking some other parts, but no luck.

FinalPrice = 
IF (AND(HASONEVALUE(FilterTable[FilterColumn]),MAX(FilterTable[FilterColumn]) = "Price with Tax"),
    MAX(SalesTable[Price with Tax]),

IF(AND(HASONEVALUE (FilterTable[FilterColumn]),MAX(FilterTable[FilterColumn]) = "Price without Tax"),MAX(SalesTable[Price without Tax]), 

IF (
        NOT (ISFILTERED ( FilterTable[FilterColumn]) ) ),
        BLANK ()
    )
))

[TomMartens]

Hey,

if you need the RowLevel of your 'SalesTable', you have to use MAXX() one of the table iterator functions, like

MAXX('SalesTable', 'SalesTable'[Price with Tax])

or
MAXX('SalesTable', 'SalesTable'[Price without Tax])

Depending on your TRUE / FALSE branch in your IF statements

Hope this helps

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

As @Greg_Deckler has mentioned above, not like measures, calculate columns/tables are computed during database processing(e.g. data load/refresh) and then stored in the model, they do not response to user selections on the report.

So it is not possible to create a calculate column/table that can change dynamically with user selections on the report. Only the measure can work in this scenario.

Regards

[Greg_Deckler]

Calculated columns are not dynamic within the context of a report. They are essentially calculated at the time of data load. Can't you just use a table visualzation and add in your row identifier and your measure?