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.
Hi,
I need to add a calculated column to a table that is in the tabulr model, I need a flag that checks each job whether the first entry (based on the date) of the value in column 'From Code' is the same as
the last entry of column 'To Code' (based on the date).
I need the outcome looks like below. Job111 to be flage as 'Yes' becaue the value of 'From Code' = MA is the same as the last entry of 'To Code'= MA.
Or even a measure, it does not have to be calculated column.
Thanks in advance
Solved! Go to Solution.
Hi @Anonymous
You mean a DAX calculated column, right?
Flag =
VAR CurJob = 'Table'[Job Number]
VAR T1 = FILTER('Table','Table'[Job Number]=CurJob)
VAR MinDate = MINX(T1,[Created DateTime Local])
VAR MaxDate = MAXX(T1,[Created DateTime Local])
VAR FromCode = MAXX(FILTER(T1,'Table'[Created DateTime Local]=MinDate),[From Code])
VAR ToCode = MAXX(FILTER(T1,'Table'[Created DateTime Local]=MaxDate),[To Code])
RETURN
IF(FromCode=ToCode,"Yes","No")
Hi,
Write this calculated column formula
Flag = if(LOOKUPVALUE(Data[From Code],Data[Created DateTime Local],CALCULATE(min(Data[Created DateTime Local]),FILTER(Data,Data[Job Number]=EARLIER(Data[Job Number]))),Data[Job Number],Data[Job Number])=LOOKUPVALUE(Data[To Code],Data[Created DateTime Local],CALCULATE(max(Data[Created DateTime Local]),FILTER(Data,Data[Job Number]=EARLIER(Data[Job Number]))),Data[Job Number],Data[Job Number]),"Yes","No")
Hope this helps.
Hi @Anonymous
You mean a DAX calculated column, right?
Flag =
VAR CurJob = 'Table'[Job Number]
VAR T1 = FILTER('Table','Table'[Job Number]=CurJob)
VAR MinDate = MINX(T1,[Created DateTime Local])
VAR MaxDate = MAXX(T1,[Created DateTime Local])
VAR FromCode = MAXX(FILTER(T1,'Table'[Created DateTime Local]=MinDate),[From Code])
VAR ToCode = MAXX(FILTER(T1,'Table'[Created DateTime Local]=MaxDate),[To Code])
RETURN
IF(FromCode=ToCode,"Yes","No")
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |