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,
I have a table and in that column, when I see 18, I need a new column to replace this value by the last value < 18. In that case, the 16 should be 16, the 18 should be 16 and the 27 should be 27. There is another column with date.
Thanks
Solved! Go to Solution.
Hi @FRG,
The first part gives us the last number < 18. The formula should be like below which just replaces 16 with "lastValue".
Column = VAR lastValue = CALCULATE ( MAX ( [idSpecies] ), Table1[idSpecies] < EARLIER ( Table1[idSpecies] ) ) RETURN IF ( [idSpecies] = 18, lastValue, [idSpecies] )
The example in the snapshot shows the last value of 10.
Best Regards,
Dale
Hi @FRG,
Try this formula as a calculated column.
Column = VAR lastValue = CALCULATE ( MAX ( [Column1] ), Table3[Column1] < EARLIER ( Table3[Column1] ) ) RETURN IF ( [Column1] = 18, 16, [Column1] )
Best Regards,
Dale
Hi @v-jiascu-msft,
Two things about that:
1. It works for now but sooner or later the number in that column will change, except the 18. So when I'll see the 10, I'll want the 18 become 10. The 18 should always become the last number < 18.
2. In that formula I don't understand the reason of the first part:
VAR lastValue = CALCULATE ( MAX ( [Column1] ), Table3[Column1] < EARLIER ( Table3[Column1] ) ) RETURN
The formula IF([Column1] = 18, 16, [Colomun1]) get the same result?
Thanks
Hi @FRG,
The first part gives us the last number < 18. The formula should be like below which just replaces 16 with "lastValue".
Column = VAR lastValue = CALCULATE ( MAX ( [idSpecies] ), Table1[idSpecies] < EARLIER ( Table1[idSpecies] ) ) RETURN IF ( [idSpecies] = 18, lastValue, [idSpecies] )
The example in the snapshot shows the last value of 10.
Best Regards,
Dale
So, what you want is the EARLIER function. 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...
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 | |
85 | |
77 | |
66 |
User | Count |
---|---|
120 | |
111 | |
95 | |
83 | |
75 |