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 have two columns with Entry no and Transferred from entry no and would need to calculate a column Expected initial Entry no, that brings the initial entry no if an item has transferred ( it's differnt than zero).
It is easy to bring the value if we have one transfer for example entry 2 but I couldnt find a solution where you have multiple transfers. Eg. entry no 5, the initial entry no is 1.
Any help would be much appreciated.
Solved! Go to Solution.
hi, @Dorinb
Based on my research, you could try this way to get it:
Step1:
In Edit Queries, repleace "0" with null for [Transferred from entry no] column.
Step2:
Create a new calculate column for Entry No which is not transformmed.
WithoutTransferred = IF('Table'[Transferred from entry no]=BLANK(),'Table'[Entry no])
Step3:
Then use this formula to create the result column:
initial entry = VAR InitialentryNo=PATHITEM(PATH('Table'[Entry no],'Table'[Transferred from entry no]),1) return LOOKUPVALUE('Table'[WithoutTransferred],'Table'[Entry no],VALUE(InitialentryNo))
Result:
By the way, I changed the last row data for test.
and here is sample pbix file, please try it.
Regards,
Lin
hi, @Dorinb
Based on my research, you could try this way to get it:
Step1:
In Edit Queries, repleace "0" with null for [Transferred from entry no] column.
Step2:
Create a new calculate column for Entry No which is not transformmed.
WithoutTransferred = IF('Table'[Transferred from entry no]=BLANK(),'Table'[Entry no])
Step3:
Then use this formula to create the result column:
initial entry = VAR InitialentryNo=PATHITEM(PATH('Table'[Entry no],'Table'[Transferred from entry no]),1) return LOOKUPVALUE('Table'[WithoutTransferred],'Table'[Entry no],VALUE(InitialentryNo))
Result:
By the way, I changed the last row data for test.
and here is sample pbix file, please try it.
Regards,
Lin
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |