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.
Hey!
Been trying for a long time now to figure out how I can write dax which:
1. Finds the row that are empty (should have contained a string, which is the name of a project)
2. Evaluates which non-empty rows that are not empty, have the same ID and are closest to the empty row in time
3. Then fills out the empty row with the project name that is considered the best match.
ID | Date | Project |
10139 | 31.10.2019 | Project 1 |
10137 | 01.11.2019 | Project 2 |
10137 | 15.11.2019 | [Empty row] |
I have achieved to write a formula which finds the closest non-empty date sorted on ID, but how do I make it change ID number based on which row it is evaluating?
My code so far:
CALCULATE (
LASTNONBLANK ( 'Kjøretøydata'[Project]; 1 );
FILTER (
'Table1';
'Table1'[Date]<= EARLIER ('Table1'[Date] )
&& NOT ( ISBLANK ('Table1'[Project]))
&& 'Table1'[ID]="10137")
)
So the only thing missing now is replacing "10137" with some DAX that dynamically takes the value of the row being evaluated. Any tips on what function I should use to achieve this?
Solved! Go to Solution.
Showed out my first suggestion were pretty close to a solution.
This gives you the closest date to any ID at any point in time:
CALCULATE (
LASTNONBLANK ( 'Table1'[Project]; 1 );
FILTER (
'Table1';
'Table1'[Date]<= EARLIER ('Table1'[Date] )
&& NOT ( ISBLANK ('Table1'[Project]))
&& 'Table1'[ID]=earlier('Table1'[ID]))
)
In order to find the last registered project I could now filter on this date:
Project =
if(isblank('Table1'[Project]);
CALCULATE(LASTNONBLANK('Table1'[Project];'Table1'[Project]);
FILTER('Table1';
'Table1'[Date]=EARLIER('Table1'[ClosestDate])&&
'Table1'[ID]=EARLIER('Table1'[ID])));
'Table1'[Project])
There were already several threads dealing with a similar issue, but none that extracted everything from the same table as far as I could see.
Showed out my first suggestion were pretty close to a solution.
This gives you the closest date to any ID at any point in time:
CALCULATE (
LASTNONBLANK ( 'Table1'[Project]; 1 );
FILTER (
'Table1';
'Table1'[Date]<= EARLIER ('Table1'[Date] )
&& NOT ( ISBLANK ('Table1'[Project]))
&& 'Table1'[ID]=earlier('Table1'[ID]))
)
In order to find the last registered project I could now filter on this date:
Project =
if(isblank('Table1'[Project]);
CALCULATE(LASTNONBLANK('Table1'[Project];'Table1'[Project]);
FILTER('Table1';
'Table1'[Date]=EARLIER('Table1'[ClosestDate])&&
'Table1'[ID]=EARLIER('Table1'[ID])));
'Table1'[Project])
There were already several threads dealing with a similar issue, but none that extracted everything from the same table as far as I could see.
Measure =
VAR _maxdate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[ID]),'Table'[Project]<>BLANK())
RETURN CALCULATE(MAX('Table'[Project]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]=_maxdate))
please try this measure
Thanks for your suggestion Vimal.
But it foes not work either unfortuantely as I want the formula to use the registered project at the closest date at any point in time, also when this is not the project registered last.
Any idea how to achieve this?
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 |
---|---|
55 | |
25 | |
23 | |
15 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
18 | |
12 |