Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
A simple problem which I haven't quite been able to find the solution for in previous posts, but have found close.
Have the folloiwng table:
Reference | Date | Value Method |
a | 20/08/2022 | method a |
b | 20/03/2022 | method a |
a | 24/04/2022 | method a |
c | 01/07/2021 | method b |
b | 01/02/2022 | method a |
a | 02/02/2022 | method b |
c | 01/05/2021 | method c |
I need to be able to provide a Y or an N for each row here where the Value Method has changed since last time based on the date and Reference. I think a calculated column is what I had in mind for this where it checks the previous most recent date for that reference and sees whether there is a difference in the Value Method, placing Y if there is or N if there isn't.
How can I achieve this?
Solved! Go to Solution.
Hi @julesdude ,
According to your description, here's my solution, create a calculated column.
Column =
VAR _Date =
MAXX (
FILTER (
'Table',
'Table'[Reference] = EARLIER ( 'Table'[Reference] )
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Date]
)
VAR _lastM =
MAXX (
FILTER (
'Table',
'Table'[Reference] = EARLIER ( 'Table'[Reference] )
&& 'Table'[Date] = _Date
),
'Table'[Value Method]
)
RETURN
IF ( _lastM = BLANK (), "N", IF ( 'Table'[Value Method] = _lastM, "N", "Y" ) )
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @julesdude ,
According to your description, here's my solution, create a calculated column.
Column =
VAR _Date =
MAXX (
FILTER (
'Table',
'Table'[Reference] = EARLIER ( 'Table'[Reference] )
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Date]
)
VAR _lastM =
MAXX (
FILTER (
'Table',
'Table'[Reference] = EARLIER ( 'Table'[Reference] )
&& 'Table'[Date] = _Date
),
'Table'[Value Method]
)
RETURN
IF ( _lastM = BLANK (), "N", IF ( 'Table'[Value Method] = _lastM, "N", "Y" ) )
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@julesdude , Create a new column like
new colu=
var _max = maxx(filter(Table, [Reference] = earlier([Reference]) && [Date] < earlier([Date]) ) , [Date])
return
if( [Method] <> maxx(filter(Table, [Reference] = earlier([Reference]) && [Date] =_max ) , [Method]) , "N", "Y")
Been trying to adapt this, it is still not working.
The table with the new column becomes as follows:
If we look at the first two rows - both have the same reference - 'a'. The top row is saying 'Y' - that yes the Value Method name has changed since the previous date. This should be N because it hasn't changed.
Reference b is also incorrect - both rows should have N as the Value method has not changed.
And c is also incorrect - the two rows with c are different, so the first row dated 01 July 2021 should be Y as the Value Method has changed since the previous date.
Grateful for any help to amend this logic please.
Hi @amitchandak thank you for helping again!
Applying the solution it is not giving me the results I was expecting.
I'll exaplin again the logic.
Basically for each row, the previous date for the reference should be found and then if the Value Method is different then it should return 'Y' in the column. If it is not different for the previous date for the reference, or if there is no previous date that can be found on record, then it should return 'N'.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
68 |
User | Count |
---|---|
226 | |
129 | |
120 | |
84 | |
78 |