Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table which collected data from a realtime sources, sometimes it happens that the collections fails for some reason on some assets, for the missing values I use below DAX (LASTNONBLANK) to fill the blanks with the most recent value at that point in time.
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])
Now I want 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.)
Can some one help?
Solved! Go to Solution.
Hey, @Chimsie
I've modified the data to reproduce your scenario. The pbix file is attached at the end.
Mesa:
You can create a calculated column as shown below.
Result =
COALESCE(
[Value],
var _id=[AssetID]
var _date=[Date]
var lastnoblankdate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[AssetID]=_id&&
'Table'[Date]<_date&&
'Table'[Value]<>BLANK()
)
)
var val=
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[AssetID]=_id&&
'Table'[Date]=lastnoblankdate
)
)
return
IF(
[Date]-lastnoblankdate<=3,
val,
0
)
)
Result:
Best regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hi, @Chimsie
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a calculated column as below.
Result =
var _asserid=[AssetID]
var _date=[Date]
var tab=
CALCULATETABLE(
DISTINCT('Table'[Date]),
TOPN(
3,
FILTER(
'Table',
'Table'[AssetID]=_asserid&&
'Table'[Date]<_date&&
'Table'[Value]<>BLANK()
),
'Table'[Date]
)
)
return
IF(
MAXX(
tab,
[Date]
)<[Date]-3,
0,
CALCULATE(
MAX('Table'[Value]),
FILTER(
'Table',
'Table'[AssetID]=_asserid&&
'Table'[Date] in tab
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-alq-msft
I don't think I was completely clear.
When for an AssetID the Value is 0/blank the result should be the LASTNONBLANK, when the Value for an AssetID is 0/blank for 3 days in a row the result should be 0 as from the fourth day.
See below example:
Hey, @Chimsie
I've modified the data to reproduce your scenario. The pbix file is attached at the end.
Mesa:
You can create a calculated column as shown below.
Result =
COALESCE(
[Value],
var _id=[AssetID]
var _date=[Date]
var lastnoblankdate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[AssetID]=_id&&
'Table'[Date]<_date&&
'Table'[Value]<>BLANK()
)
)
var val=
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[AssetID]=_id&&
'Table'[Date]=lastnoblankdate
)
)
return
IF(
[Date]-lastnoblankdate<=3,
val,
0
)
)
Result:
Best regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Great, thank you very much! 🤗
Nobody who can help me with the above challenge, is it possible at all?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |