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.
@Anonymous
In this scenario, since you need to evaluate a dynamic context based on selection, you can only use measure to get corresponding values. In calculated column, you can only give a fixed context.
Calculated Columns and Measures in DAX
Regards,
Hi,
if it was me I'd create two measures (one for Price with Tax and one without tax)
then my final price measure would be something like this.
FinalPrice =
var Filt = IF (HASONEVALUE(FilterTable[FilterColumn]), VALUES(FilterTable[FilterColumn]), BLANK())
RETURN
SWITCH(Filt,
"Price with Tax",[Price with Tax_measure]
"Price without Tax", [Price without Tax_measure],
BLANK()
)
@Anonymous
In this scenario, since you need to evaluate a dynamic context based on selection, you can only use measure to get corresponding values. In calculated column, you can only give a fixed context.
Calculated Columns and Measures in DAX
Regards,
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |