The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Sorry if this has come up before but new user and trying to get my head around some of the functions in PowerBI. I have a series of ~ 20 million sample rows for which I am attributing values based on the sample results in the table. I am using the function "Column = LOOKUPVALUE('Result Scores'[Score],'Result Scores'[Code],[Result])" to cross reference the results to the corresponding value. The data looks something like this:
Sample Number Date Result Value
Sample1 1/1/2000 FOU 3
Sample2 1/2/2000 KNA 3
Sample3 1/3/2000 QSB 2
Sample4 1/4/2000 LSD 3
Sample5 1/5/2000 ICN 0
Sample6 1/6/2000 ICN 0
Sample7 1/7/2000 ICN 0
Sample8 1/8/2000 ICN 0
Sample9 1/9/2000 TGS 3
Sample10 1/10/2000 MHG 2
Sample11 1/11/2000 MQV 2
Sample12 1/12/2000 NOE 3
Sample13 1/13/2000 KRW 1
Sample14 1/14/2000 QXV 3
Sample15 1/15/2000 FIB 1
As you can see, result value "ICN" pulls a 0 value. There are multiple such results that could pull a 0 instead of value 1-3, and they can be sequential as they appear in the above. Since the ICN results follow result LSD, I want each of the ICN results to carry over the value 3, not 0. This would be true whether it was a 1, 2 or 3. The results with a 0 value should carry the value of the previous sample indefinitely until a sample with a non-zero value comes up. I know I can use IF with LOOKUPVALUE to reference a previous row's result, but I am not sure how to continuously use that result until a non-zero value is returned.
Solved! Go to Solution.
Hi @erdvige
Try adding this calculated Column
New values = VAR LastNonZeroDate = MAXX ( FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) && Table1[Value] <> 0 ), Table1[Date] ) RETURN IF ( Table1[Value] = 0, CALCULATE ( VALUES ( Table1[Value] ), FILTER ( ALL ( Table1 ), Table1[Date] = LastNonZeroDate ) ), Table1[Value] )
Hi @erdvige
Try adding this calculated Column
New values = VAR LastNonZeroDate = MAXX ( FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) && Table1[Value] <> 0 ), Table1[Date] ) RETURN IF ( Table1[Value] = 0, CALCULATE ( VALUES ( Table1[Value] ), FILTER ( ALL ( Table1 ), Table1[Date] = LastNonZeroDate ) ), Table1[Value] )
So this is working on a sample set of data. But when I expand it into the full list with 20 mil+ records I'm forced to have two new columns: the one you proposed and one which concatenates a unique identifier for each row combined with the date/time value, since the date/time has nonunique values. But in doing this I'm now overtaxing my system. The solution works so long as it's not too much data. I may need to find a different approach.
What is your formula for VALUE column?
May be I can adjust it directly?
So the two new columns I created:
Prelim Score | ID&Date |
1 | 10164842250 |
1 | 10164842251 |
1 | 10164842252 |
1 | 10164842253 |
2 | 10164842254 |
1 | 10164842255 |
1 | 10164842256 |
3 | 10164842257 |
3 | 10164842258 |
3 | 10164842259 |
0 | 10164842260 |
0 | 10164842261 |
0 | 10164842262 |
0 | 10164842263 |
1 | 10164842264 |
1 | 10164842265 |
1 | 10165542257 |
1 | 10165542258 |
1 | 10165542259 |
2 | 10165542260 |
2 | 10165542261 |
2 | 10165542262 |
1 | 10165542263 |
1 | 10165542264 |
1 | 10165542265 |
1 | 10165542266 |
0 | 10165542267 |
0 | 10165542268 |
3 | 10165542269 |
0 | 10165542270 |
1 | 10165542271 |
1 | 10165542272 |
The ID&Date column concatenates the ID of the sample series with the numerical value of the date on which it was done. All date values are whole numbers, the data is sorted primarily by ID and then by date. From there I modified your code to refer to these two columns:
New values = VAR LastNonZeroDate = MAXX ( FILTER ( 'History Sheets', 'History Sheets'[ID&Date] < EARLIER ( 'History Sheets'[ID&Date] ) && 'History Sheets'[Prelim Score] <> 0 ), 'History Sheets'[ID&Date] ) RETURN IF ( 'History Sheets'[Prelim Score] = 0, CALCULATE ( VALUES ( 'History Sheets'[Prelim Score] ), FILTER ( ALL ( 'History Sheets' ), 'History Sheets'[ID&Date] = LastNonZeroDate ) ), 'History Sheets'[Prelim Score] )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
79 | |
72 | |
48 | |
39 |
User | Count |
---|---|
138 | |
108 | |
69 | |
64 | |
57 |