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.
Hi Team,
i have this function to calculate difference between rows:
QF Trade Ups2 =
VAR myindex=
selectedvalue('QF Core Pricing'[Fare Index - Text])
return
[MIN AIF QF]
-CALCULATE([MIN AIF QF],FILTER(ALL('QF Core Pricing'),VALUE('QF Core Pricing'[Fare Index - Text])=value(myindex)-1)
)
which leads to something weird:
Any idea why
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Typically you would need to use EARLIER to achieve what you are going for.
Hi,
why should I use Earlier? I don't have a time comparison to achieve.
Sorry too long, don't have time.
Read what you were doing too quickly. So, I assume that this is a measure. I would try MAX instead of SELECTEDVALUE. SELECTEDVALUE is typically used when dealing with a slicer. MAX of your Fare Index in the context of a row should return the value of the Fare Index in that row. Not sure what SELECTEDVALUE would return for something like that.
Sample source data would allow me to try different things out and be specific with a solution. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
either SELECTEDVALUE or MAX just comes to the same situation where there is data make no sense:
I'm sorry i don't have time to go the whole answer and to give to you an example:
Index | AIF | Tradeup |
1 | 100 | 0 |
2 | 250 | 150 |
3 | 300 | 50 |
4 | 450 | 150 |
i just need the difference between rows 😄
Hi @Anonymous,
We can create a measure to work on it.
Measure = VAR index = MAX ( Table1[Index ] ) RETURN IF ( index = 1, 0, CALCULATE ( SUM ( Table1[AIF] ) ) - CALCULATE ( SUM ( Table1[AIF] ), FILTER ( ALL ( Table1 ), Table1[Index ] = index - 1 ) ) )
Also please find the pbix attached.
Regards,
Frank
Hi Frank,
mmm I believe we have to include more variables. This is what I got
Trade Up = VAR index = max('QF Core Pricing'[Fare Index]) return if( index = 1, 0, calculate(sum('QF Core Pricing'[AIF]))- calculate(sum('QF Core Pricing'[AIF]),FILTER(ALL('QF Core Pricing'),'QF Core Pricing'[Fare Index]= index-1) ) )
Columns Market and restriction are relevant. It looks like this measure is just doing it for the whole query.
What if we make it as a column?
Hi @Anonymous,
To create a calculated column as below.
Column = var index = CALCULATE(MAX(Table1[Index ])) return IF(index=1,0,Table1[AIF] -CALCULATE(SUM(Table1[AIF]),FILTER(Table1,Table1[Index ]=index-1)))
Regards,
Frank
No Luck again:
Trade Up = var index=Calculate(MAX('QF Core Pricing'[Fare Index])) return if(index=1,0,'QF Core Pricing'[AIF]-calculate(sum('QF Core Pricing'[AIF]),Filter('QF Core Pricing','QF Core Pricing'[Fare Index]=index-1)))
could it be that it needs a filter for the market and restriction columns in order to work?
Hi @Anonymous,
Is that possbile to share your pbix to me? You can upload the file to dropbox and share the link to me.
Regards,
Frank
Hi Frank,
use this one, where sensible data has been removed:
https://www.dropbox.com/s/r7pdon0duhtm55s/Fare%20Grids%20-%20msft.pbix?dl=0
let me know if doesn't work.
cheers
Alberto
OK, I created both a column using EARLIER and a measure.
This is the query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YlWMgKyjUwhbGMg2xgqbgJkm4DEYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, AIF = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"AIF", Int64.Type}}) in #"Changed Type"
This is the column:
Column = VAR __previous = MAXX(FILTER(ALL(Table1),[Index]=EARLIER([Index])-1),[AIF]) RETURN IF(ISBLANK(__previous),0,[AIF]-__previous)
And this is the measure:
Measure = VAR __index = MAX([Index]) VAR __previous = MAXX(FILTER(ALL(Table1),[Index]=__index-1),[AIF]) RETURN IF(ISBLANK(__previous),0,MAX([AIF])-__previous)
Also attached.
Sorry Man,
no luck with this 2 solutions:
QF Trade Up = VAR __index = MAX([Fare Index]) VAR __previous = MAXX(FILTER(ALL('QF Core Pricing'),[Fare Index]=__index-1),[AIF]) RETURN IF(ISBLANK(__previous),0,MAX([AIF])-__previous)
Trade Up column = VAR __previous = MAXX(FILTER(ALL('QF Core Pricing'),[Fare Index]=EARLIER([Fare Index])-1),[AIF]) RETURN IF(ISBLANK(__previous),0,[AIF]-__previous)
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |