Forum Discussion
Calculated Column Based on Unrelated, Filtered Table
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 ()
)
))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. :smileyhappy:
Regards
3 Replies
- Greg_DecklerCommunity Champion
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?
- v-ljerr-msftMicrosoft Employee
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. :smileyhappy:
Regards
- TomMartensSuper User
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 statementsHope this helps