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.
IF I were looking for a Pseudo Code description of my problem it would be this:
-- IF(IsBlank(Value1),Lookup(Value2) RETURN First NON BLANK (Value1)Else(Lookup(Value2) NEXT Row IN Column
Sample Data:
Change Date Status Iteration WorkID
1/1/2020 NEW NULL 12345
1/2/2020 TRIAGE NULL 12345
1/2/2020 ACTIVE W1 12345
1/3/2020 REVIEW W2 12345
1/4/2020 DONE W2 12345
1/2/2020 NEW NULL 54321
1/3/2020 ACTIVE W1 54321
1/4/2020 DONE W1 54321
I need a DAX Query that says IF the Iteration value is NULL then look to the next value in that column based on the WorkID.
AND
If that Next Value ISBLANK Lookup(Again) Next Value..
So that the data fills in the NULL values like this:
Change Date Status Iteration WorkID
1/1/2020 NEW W1 12345 Here STATUS lookup the Work ID by sequence two column values to
1/2/2020 TRIAGE W1 12345 find a valid iteration. SO IF(ISBLANK Lookup AND IF(ISBLANK)
1/2/2020 ACTIVE W1 12345
1/3/2020 REVIEW W2 12345
1/4/2020 DONE W2 12345
1/2/2020 NEW W1 54321
1/3/2020 ACTIVE W1 54321
1/4/2020 DONE W1 54321
Hope this example is clear but if more details are needed please advise. Any guidance you can offer would be greatly appreciated.
Solved! Go to Solution.
Hi @v-merpet ,
We can create a calculated column using following DAX to meet your requirement:
Iteraction_New =
IF (
ISBLANK ( 'Table'[Iteraction] )
|| 'Table'[Iteraction] = "",
CALCULATE (
FIRSTNONBLANK ( 'Table'[Iteraction], TRUE () ),
FILTER (
'Table',
'Table'[WorkID] = EARLIER ( 'Table'[WorkID] )
&& 'Table'[Change Date] >= EARLIER ( 'Table'[Change Date] )
&& 'Table'[Iteraction] <> ""
)
),
[Iteraction]
)
Best regards,
Hi @v-merpet ,
We can create a calculated column using following DAX to meet your requirement:
Iteraction_New =
IF (
ISBLANK ( 'Table'[Iteraction] )
|| 'Table'[Iteraction] = "",
CALCULATE (
FIRSTNONBLANK ( 'Table'[Iteraction], TRUE () ),
FILTER (
'Table',
'Table'[WorkID] = EARLIER ( 'Table'[WorkID] )
&& 'Table'[Change Date] >= EARLIER ( 'Table'[Change Date] )
&& 'Table'[Iteraction] <> ""
)
),
[Iteraction]
)
Best regards,
This is more easily accomplished in power query, if you don't mind changing the dataset to include these values.
Simply go to edit query, select the iteration column, and choose fill up.
If you need some help with this, just ping me on teams.
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.