Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
key_to
Advocate I
Advocate I

Convert measure to column

Hi,

 

I created a measure that works fine for each individual row, but the total sum and total average by division are wrong. Therefor I need to convert the measure into the column. Therefore I need to change the measure into column. 

 

The new calculated column needs to look up current value, if the current value is blank, it needs to find the previous value and add it into the row value.  So basically something like this:

Looks at current salary amount, if current salary > 0 then use current salary amount, if current salary = empty or zero, find previous salary based on date and use this alternative amount. I managed to get this to work as a measure in DAX but I need this to be converted into a column: 

 

The dax measure is following: 

Adjsd BasePay =
VAR previousDate =
    CALCULATE (
        MAX ( 'PBI Compensation'[Date] ),
        FILTER ( ALL ('PBI Compensation'),'PBI Compensation'[Amount] > 0 && 'PBI Compensation'[Date] < MAX ( 'PBI Compensation'[Date] ) )
    )
VAR previousSalary =
    CALCULATE (
        SUM ( 'PBI Compensation'[Amount] ),
        FILTER ( ALL ( 'PBI Compensation'), 'PBI Compensation'[Date]= previousDate)
    )
    VAR currentSalary =
    CALCULATE (
        SUM ( 'PBI Compensation'[Amount] ),
        FILTER ( ALL ( 'PBI Compensation'), 'PBI Compensation'[Date]= MAX ('PBI Compensation'[Date])
    )
) RETURN  If (currentSalary=0,previousSalary,currentSalary)
 
Appreciate any help!
Thanks in advance! 
1 ACCEPTED SOLUTION

I have found a solution for my issue and posting it here in case somebody else comes accross similar issue as me.
Adjusted BaseSalary NOK column =
 VAR currentRowDate = 'PBI Compensation'[Date]
 VAR currentSalary = 'PBI Compensation'[BasePay in NOK]
 VAR previousSalary = LOOKUPVALUE('PBI Compensation'[BasePay in NOK], 'PBI Compensation'[SuccessFactor ID], 'PBI Compensation'[SuccessFactor ID], 'PBI Compensation'[Date], CALCULATE(MAX('PBI Compensation'[Date]), FILTER('PBI Compensation', 'PBI Compensation'[Date] < currentRowDate)))
 RETURN IF(ISBLANK(currentSalary), previousSalary, currentSalary)

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@key_to , Previous salary as new column

 

new COlumn =

var _max = maxx(filter( 'PBI Compensation', 'PBI Compensation'[Date] < earlier( 'PBI Compensation'[Date] )), 'PBI Compensation'[Date] )

return

Sumx(filter( 'PBI Compensation', 'PBI Compensation'[Date] = _max ), 'PBI Compensation'[Amount] )

 

 

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

I have found a solution for my issue and posting it here in case somebody else comes accross similar issue as me.
Adjusted BaseSalary NOK column =
 VAR currentRowDate = 'PBI Compensation'[Date]
 VAR currentSalary = 'PBI Compensation'[BasePay in NOK]
 VAR previousSalary = LOOKUPVALUE('PBI Compensation'[BasePay in NOK], 'PBI Compensation'[SuccessFactor ID], 'PBI Compensation'[SuccessFactor ID], 'PBI Compensation'[Date], CALCULATE(MAX('PBI Compensation'[Date]), FILTER('PBI Compensation', 'PBI Compensation'[Date] < currentRowDate)))
 RETURN IF(ISBLANK(currentSalary), previousSalary, currentSalary)

@amitchandak Thanks a million for looking into my issue! I tried your solution but unfortunately " Earlier" does not seem to work in my context: I get the following error: 

 

key_to_0-1677077899131.png

 

Not sure why but maybe the reason is that the reffered date column is not a continous date table (it only contains the last day of each month)?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.