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 there, I am trying to convert below given excel formula into power bi formula:
: =IF((I4<>I5)*(E4="No"),"Current",IF((I4<>I5)*(E4="Yes"),"FDR","Not relevant"))
I have created the index row starting with 0, 1.. to find the previous value - however, the return value isn't correct in power bi. below is my formula:
: Column FDR = IF([Position ID] <> [Last Position ID]*([4. Today]="No"), "Current", IF([Position ID] <> [Last Position ID]*([4. Today]="YES"),"FDR", "Not Relevant"))
The return values are only "Current" and "FDR". Although, FDR only should come in YES but it shows for No and Current shows for Yes & No. I am not sure what i am missing in this formula...
Really appreciate your input in this problem..
Hi @Anonymous ,
According to your description, here’s my solution.
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.
@Anonymous provide sample data and desired output.
Hi, thank you for responding..
Position ID is the data set and first three columns are excel formula columns.
1. Concat - just random date generated in system and i changed the format to (dd/mm/yyyy).
2. > Today? - =IF(D3*1>TODAY(),"Yes","No")
3. FDR =IF((G3<>G4)*(E3="No"),"Current",IF((G3<>G4)*(E3="Yes"),"FDR","Not relevant"))
So technically, if position ID G3<>G4 and >Today is No = the return value should be Current.
If the psoition id G3<>G4 and > Today is Yes = the return value should be FDR (if true) Not relevant (if false)
Column D | E | F | G | |
Concat | > Today? | FDR? | Position ID | |
3 | 3/4/2019 | No | Not relevant | 50000457 |
4 | 30/4/2019 | No | Not relevant | 50000457 |
5 | 20/6/2019 | No | Not relevant | 50000457 |
6 | 1/9/2019 | No | Not relevant | 50000457 |
7 | 24/10/2019 | No | Not relevant | 50000457 |
8 | 7/11/2019 | No | Not relevant | 50000457 |
9 | 26/3/2021 | No | Not relevant | 50000457 |
10 | 1/4/2021 | No | Not relevant | 50000457 |
11 | 13/9/2021 | No | Not relevant | 50000457 |
12 | 1/10/2021 | No | Current | 50000457 |
In power bi - i have created index column and index_1 to get the last position ID (below cell position id). but the return value in power bi is not as expected. Below was the if function measure i used in power bi.
Column FDR = IF([Position ID] <> [Last Position ID]*([4. Today]="No"), "Current", IF([Position ID] <> [Last Position ID]*([4. Today]="YES"),"FDR", "Not Relevant"))
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |