Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 () ) ))
Solved! Go to 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
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
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |