Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculated Column Based on Unrelated, Filtered Table

Currently, I have 2 tables : SalesTable and FilterTableSalesTable 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_Deckler's avatar
    Greg_Deckler
    Community 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-msft's avatar
    v-ljerr-msft
    Microsoft 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

  • 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