Forum Discussion
Responsive ResidentIf Customer with Same Country and Same GLD = Same Project
HI,
Happy Friday. I need help with one formula I have in Excel.
=IF(A2<>A3,1,IF(AND(A2=A3,D2<>D3),1,IF(AND(A2=A3,D2=D3,E2=E3),0,1)))
Where A is CID Number
Where D is Country
Where E is GLD
The idea is to count the number of projects. If a Customer (CID) have CUIDs with the same country and Same GLD (Go live Date)
From the table below, the ones highlighted in red are considered 1 project.
| CID | Client Name | Id Country | CUID | Country | GLD | Num Projects |
| 10002 | Customer A | US | CustA1 | United States | 9/1/2022 | 0 |
| 10002 | Customer A | US | CustA2 | United States | 9/1/2022 | 1 |
| 10022 | Customer B | IN | CustB1 | India | 12/1/2021 | 1 |
| 10022 | Customer B | SE | CustB2 | Sweden | 4/1/2024 | 1 |
| 10027 | Customer C | AU | CustC1 | Australia | 5/1/2022 | 1 |
| 10038 | Customer D | EG | CustD1 | Egypt | 3/1/2024 | 1 |
| 10047 | Customer E | SG | CustE1 | Singapore | 7/1/2022 | 1 |
| 10065 | Customer F | SG | CustF1 | Singapore | 6/1/2022 | 1 |
| 10109 | Customer G | AT | CustG1 | Austria | 8/1/2022 | 1 |
| 10109 | Customer G | BR | CustG2 | Brazil | 6/1/2023 | 0 |
| 10109 | Customer G | BR | CustG3 | Brazil | 6/1/2023 | 0 |
| 10109 | Customer G | BR | CustG4 | Brazil | 6/1/2023 | 1 |
| 10109 | Customer G | CR | CustG5 | Costa Rica | 7/1/2022 | 1 |
| 10109 | Customer G | DK | CustG6 | Denmark | 5/1/2023 | 1 |
| 10109 | Customer G | EE | CustG7 | Estonia | 11/1/2021 | 1 |
| 10109 | Customer G | EE | CustG8 | Estonia | 2/1/2024 | 1 |
| 10109 | Customer G | DE | CustG9 | Germany | 10/1/2021 | 1 |
| 10109 | Customer G | GR | CustG10 | Greece | 10/1/2021 | 1 |
| 10109 | Customer G | KR | CustG11 | Korea, Republic Of | 2/1/2022 | 1 |
| 10109 | Customer G | LV | CustG12 | Latvia | 10/1/2021 | 0 |
| 10109 | Customer G | LV | CustG13 | Latvia | 10/1/2021 | 1 |
| 10109 | Customer G | LT | CustG14 | Lithuania | 10/1/2021 | 1 |
| 10109 | Customer G | LU | CustG15 | Luxembourg | 1/1/2024 | 1 |
| 10109 | Customer G | MX | CustG16 | Mexico | 2/1/2022 | 0 |
| 10109 | Customer G | MX | CustG17 | Mexico | 2/1/2022 | 1 |
| 10109 | Customer G | NO | CustG18 | Norway | 2/1/2024 | 1 |
| 10109 | Customer G | PA | CustG19 | Panama | 7/1/2022 | 1 |
| 10109 | Customer G | RS | CustG20 | Serbia | 2/1/2024 | 1 |
| 10109 | Customer G | SG | CustG21 | Singapore | 2/1/2022 | 1 |
for the Excel formula to work, I have the Excel sorted to get CID/Country and GLD (from Oldest to Newest). and the formula filters if Same CID, Same COuntry (from previous row) And Same GLD (from previous Row)
= 0 and if not = 1. Then I can count the number of projects.
Trying to get the same in PowerBI but not with good results. Any tip is welcome
thanks,
It seems I forgot an "OR" for the final filter.
NumProjects = IF (OR ( OR ('Weekly Slippage'[CID]<>CALCULATE (MAX ('Weekly Slippage'[CID]),FILTER ('Weekly Slippage','Weekly Slippage'[Index] = EARLIER ('Weekly Slippage'[Index])-1)),'Weekly Slippage'[Country]<>CALCULATE (MAX ('Weekly Slippage'[Country]),FILTER ('Weekly Slippage','Weekly Slippage'[Index] = EARLIER ('Weekly Slippage'[Index])-1))),'Weekly Slippage'[Ops Forecast & Actuals]<>CALCULATE(MAX('Weekly Slippage'[Ops Forecast & Actuals]),FILTER('Weekly Slippage','Weekly Slippage'[Index] = EARLIER('Weekly Slippage'[Index]) -1))),1,0)If you want the rows with '1' to display the sum of 'Contract EE' and the coresponding range then change the value in the _size variable.
FROM
var _size = IF( [Num Projects] = 1, [Contract EE], CALCULATE(SUM('Table'[Contract EE]),ALLEXCEPT('Table', 'Table'[Id Country], 'Table'[GLD])) )TO
var _size = IF( [Num Projects] = 0, [Contract EE], CALCULATE(SUM('Table'[Contract EE]),ALLEXCEPT('Table', 'Table'[Id Country], 'Table'[GLD])) )
16 Replies
- AmiraBedh
Super User
You need to sort by CID, Country, and GLD then create a calculated column that replicates the logic of your Excel formula :
Num Projects = VAR CurrentRow = TableName VAR PreviousRow = CALCULATETABLE( TableName, TableName[Index] = CurrentRow[Index] - 1 ) RETURN IF ( CurrentRow[CID] <> PreviousRow[CID], 1, IF ( AND( CurrentRow[CID] = PreviousRow[CID], CurrentRow[Country] <> PreviousRow[Country] ), 1, IF ( AND( CurrentRow[CID] = PreviousRow[CID], CurrentRow[Country] = PreviousRow[Country], CurrentRow[GLD] = PreviousRow[GLD] ), 0, 1 ) ) )
romovaroThanks Amira
I created a custom Sort in Power Query: CID/Country and GLD
and Index Column
but when creating the calculated column, I have some issues with the variables Current row/Previous Row
Am I making a mistake?
I also have the error: Too many arguments have been passed to the AND function. The maximum number of arguments for the function is 2.
Thanks,
g
- romovaro
Hi AmiraBedh
I found a different solution but not working for the 3 filters I want to apply.
After Sorting and creating an Index formula.
IF same Customer 1 or 0 it works:
Project = IF('Weekly Slippage'[CID]<>CALCULATE(MAX ('Weekly Slippage'[CID]),FILTER ('Weekly Slippage','Weekly Slippage'[Index]=EARLIER('Weekly Slippage'[Index])-1)),1,0)When same customer and COuntry 1 or 0, it works:Project2 = IF(OR('Weekly Slippage'[CID]<>CALCULATE (MAX ('Weekly Slippage'[CID]),FILTER ('Weekly Slippage','Weekly Slippage'[Index] = EARLIER ('Weekly Slippage'[Index])-1)),'Weekly Slippage'[Country]<>CALCULATE(MAX('Weekly Slippage'[Country]),FILTER('Weekly Slippage','Weekly Slippage'[Index] = EARLIER('Weekly Slippage'[Index]) -1))),1,0)But when I try the 3 filters. Same Customer/Country and Date...I have an error but I don't know why:NumProjects = IF (OR('Weekly Slippage'[CID]<>CALCULATE (MAX ('Weekly Slippage'[CID]),FILTER ('Weekly Slippage','Weekly Slippage'[Index] = EARLIER ('Weekly Slippage'[Index])-1)),'Weekly Slippage'[Country]<>CALCULATE (MAX ('Weekly Slippage'[Country]),FILTER ('Weekly Slippage','Weekly Slippage'[Index] = EARLIER ('Weekly Slippage'[Index])-1))),'Weekly Slippage'[Ops Forecast & Actuals]<>CALCULATE(MAX('Weekly Slippage'[Ops Forecast & Actuals]),FILTER('Weekly Slippage','Weekly Slippage'[Index] = EARLIER('Weekly Slippage'[Index]) -1))),1,0)It seems the error is at the end but....ANy tip is welcomeThanks
jgeddesTry this code in Power Query. (Paste into the advanced editor of a blank query)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZXbjoIwEIZfhXhtIhSPl6hIjMfIutnEeFG165KVYiqsuk+/HYVSFCtmr+xM+/0zHX5wsSgZuq6jUrnUiQ5h4BOmWTyYu3HGMiCiXkg2mhvikBx43KoYFaQjoPTSsvxUA6k1jEQDZTTaPOiP40wb+ujTjYcBQFfYUMOuncCw5R7JhlC+qF7hagZuyHCHB9Y8znSgisUXDO8u1Ws5nZtNme/ywHbiTBd4e3veh/zXzKldzdSGnt2EtYF1PbrF+4ARvm7k1K7XZL4n871bvn7PG3pL5gG13uKMI+5+uXmzEN2eJTQcbDP86+3S0qZkGiVs/geuPoAVbXcEfJlncAixNvPWOH/qOQLdQSJQh4hQH7Pv1C9PytuJWR2wg8136NXrxr3XlXgzg6N7w+W1LmjYcgjzMT0Dohcq7ojRGTqEjJA1KY4PUhxODrhVcVmbkX202nlrbfKZ3uPJIxi+CyU4OcThz3WIUiMK/0i4+QhXVU9fG5j30Au/IkxfUpgLBTDhMDoRfxVEbAtYoSc5+hAK4MIROXnrIDs/xQAkuvGAVtQeTwQNJhwH7IjPhT04tQQNe1NMsV/85ZslfzYOAgu6hK1e8L/4YDro9ouZvfryDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CID = _t, #"Client Name" = _t, #"Id Country" = _t, CUID = _t, Country = _t, GLD = _t, #"Num Projects" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CID", Int64.Type}, {"Client Name", type text}, {"Id Country", type text}, {"CUID", type text}, {"Country", type text}, {"GLD", type date}, {"Num Projects", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Num Projects"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"CID", "Client Name", "Id Country", "Country"}, {{"_nestedTable", each _, type table [CID=nullable number, Client Name=nullable text, Id Country=nullable text, CUID=nullable text, Country=nullable text, GLD=nullable date]}}), Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.AddColumn(_, "NumProjects", (x)=> if x[Index] = List.Max([Index]) then 1 else 0)}}), #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"CUID", "GLD", "NumProjects"}, {"CUID", "GLD", "NumProjects"}) in #"Expanded _nestedTable"
- romovaro
Hi Amira
Any idea why my var is not being recognized?
thanks,
