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 everyone
I have a table which collected data from a realtime sources, sometimes it happens that the collections fails for some reason on some assets, now I am trying to populate these missing values with the most recent value (LASTNONBLANK) at that point in time.
For example:
Table1
Date AssetID Value Calculated column
10/09/2020 11202 3.42 3.42
10/09/2020 14210 6.50 6.50
10/09/2020 19500 2.21 2.21
10/09/2020 21200 7.25 7.25
11/09/2020 11202 3.66 3.66
11/09/2020 14210 null 6.50
11/09/2020 19500 null 2.21
11/09/2020 21200 7.95 7.95
12/09/2020 11202 null 3.66
12/09/2020 14210 7.12 7.12
12/09/2020 19500 null 2.21
12/09/2020 21200 null 7.25
13/09/2020 14210 6.91 etc.
13/09/2020 19500 2.01
13/09/2020 21200 null
13/09/2020 11202 3.10
An Assets ID tries to poll data every day, if this fails, null is written in the column value for this asset.
With the help of the this forum I was able the fill the blanks for a unique Asset ID with below DAX formular
=LOOKUPVALUE(Table1[Value],Table1[AssetID],Table1[AssetID],Table1[Date],
CALCULATE(LASTNONBLANK(Table1 [Date], 1),
FILTER(Table1,Table1[Value]>0 &&EARLIER(Table1 [Date])>= Table1 [Date])))
But this DAX formular does not work with multiple asset IDs
Can some one help?
Solved! Go to Solution.
Hi @Chimsie,
Please try the following calculated column
calc value =
var assetid = [AssetID]
var curDate = [Date]
var priorMaxDate = CALCULATE(MAX('Table1'[Date]), FILTER('Table1', 'Table1'[AssetID] = assetid && [Date] < curDate && 'Table1'[Value] <> BLANK()))
return
if([Value] = blank(), CALCULATE(max('Table1'[Value]), FILTER('Table1', [AssetID] = assetid && [Date] = priorMaxDate)), 'Table1'[Value])
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
@Chimsie , Try a new column like
if(isblank(Table[Value]),
CALCULATE(LASTNONBLANKvalue(Table1 [Date], max(Table[Value])), FILTER(Table1, Table1[AssetID]= EARLIER(Table1[AssetID]) && Table1 [Date] < EARLIER(Table1 [Date])))
,Table[Value] )
Hi @Chimsie,
Please try the following calculated column
calc value =
var assetid = [AssetID]
var curDate = [Date]
var priorMaxDate = CALCULATE(MAX('Table1'[Date]), FILTER('Table1', 'Table1'[AssetID] = assetid && [Date] < curDate && 'Table1'[Value] <> BLANK()))
return
if([Value] = blank(), CALCULATE(max('Table1'[Value]), FILTER('Table1', [AssetID] = assetid && [Date] = priorMaxDate)), 'Table1'[Value])
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
An additional question, is it possible to expand the formula with the following,
When it fails to collect data from a source (assetID) for 3 consecutive days, the calculated column will change to zero for that assetID. this does not have to be retroactive.
Thank you, this works perfect:-)
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |