Forum Discussion
Helper IINeed help converting calculate column to Measure
Hello Friends,
How can I convert this calculated column below to a measure?
Sales_Fee =
SWITCH(TRUE(),
'Product'[Product_Name] ="Iphone X", "250.00",
//
'Product'[Product_Name] ="Iphone R", "350.00",
//
'Product'[Product_Name] ="Iphone S" &&
'Product'[Order].[Date] < DATEVALUE("04/02/2021"),"200.00", ( replace line with Calendar table (date))
//
'Product'[Product_Name] ="Iphone S" &&
'Product'[Order].[Date] >= DATEVALUE("04/02/2021"),"250.00", ( replace line with Calendar table (date))
//
'Product'[Color] = "Gold" &&
'Product'[Product_Name] in {"Iphone S","Iphone X","Iphone R","Iphone 13"} &&
''Product'[Order].[Date] >= DATEVALUE("01/02/2019") && ( replace line with Calendar table (date))
'Product'[Order].[Date] < DATEVALUE("04/02/2021") && ( replace line with Calendar table (date))
'Product'[Status] = "Unknown", "400.00",
//
'Product'[Color] = "White" &&
'Product'[Product_Name] in {"Iphone S","Iphone X","Iphone R","Iphone 13"} &&
''Product'[Order].[Date] >= DATEVALUE("04/02/2021") && ( replace line with Calendar table (date))
'Product'[Status] = "", "350.00", "0")
I still cannot make heads or tails out of the original calculated column DAX with the syntax errors. If you want to use the date from a date table use RELATED('Date'[Date]) instead of the order date.
Otherwise read EVERYTHING I wrote in my first post and provide all of that.
6 Replies
- edhans
Community Champion
Virtually impossible without more info dakins
- Your DAX isn't correct. If I copy and paste it into DAXFORMATTER.com there are a lot of syntax errors, so I cannot see what it is doing.
- I don't know where or how you are using it. Calculated columns only have row context. Measures have Filter Context, and may or may not create row context depending on the functions used (SUMX, FILTER and more create row context)
- We have no data to test with.
It will likely involve replacing some column references with SELECTEDVALUE() on some fields but again, without knowing how it is being used, that may not work. SELECTEDVALUE() can return a value, a blank, or an alternate value depending on the visual it is in (matrix for example) and that can change as the user moves through a heirarchy in the matrix.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
dakinsedhans- Thanks for trying to help. The code I have above is for a calculated column I created with the order date in my product table. Now I am learning to use calendar table hence my order date is useless. I want to re-create the calculated column I once had as a measure.
I hope this provides context
P.s - I have a calendar table now hence the order date in the product table is useless
Below is my Product TableName Order Date Status Color Iphone S 03/02/2021 Unknown Black Iphone X 10/02/2021 White Iphone R 01/09/2022 New Gold Iphone P 10/1/2019 Used Black
- edhans
I still cannot make heads or tails out of the original calculated column DAX with the syntax errors. If you want to use the date from a date table use RELATED('Date'[Date]) instead of the order date.
Otherwise read EVERYTHING I wrote in my first post and provide all of that.
- edhans
Hey dakins - just wanted you to know I am not trying to be difficult. When I tried to understand your DAX code, I use DAX Formatter to format it so I can see what it is doing and where the commas go. This is what I get:
so it is really difficult to get started.
As for the end result, I'd need to know how you are reporting this, because as noted earlier, visuals create filter context and different visuals can apply different filters depending on what you are doing. So the info requested below is really key to getting an answer when it is as complex as yours. This isn't a simple 5 minute answer. There is no "convert calculated column to measure" wizard.
- dakins
edhansMuch appreciated. I was able to re-create the custom column by replacing order date with RELATED('Date'[Date]).
The code I posted in my inital post isn't a DAX code but a custom column code. I was looking to turn the custom column into DAX. Perhaps, it isn't possible. I am new to Power bi and learning its limitations.
Thanks again
- edhans
Glad I was able to help with the RELATED() function. A calculated column in Power BI is DAX. So is a Calculated Table, and a Measure. All DAX.
The only code that isn't DAX is M code in Power Query, but if you are in the data model, it is DAX everywhere.