How to convert this Aladin's formula to DAX ?

flora1

New Member
Joined
Nov 27, 2017
Messages
45
[FONT=&quot]I have a column "Product IDs" with 5 digit serial numbers in them and expenditure column which has amounts. then i have Year column and department column. [/FONT]

[FONT=&quot]I have this below formula. [/FONT]

[FONT=&quot]=SUMPRODUCT(ExpenditureCol[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]umn,--ISNU[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]MBER(MATCH[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot](YearColum[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]n,{2019},0[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot])),--(Depa[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]rtmentColu[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]mn="Ophtha[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]lmologist"[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]),--ISNUMB[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]ER(MATCH(L[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]EFT(Produc[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]tIDColumn&[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]"#",1),{"3[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]"},0)),1-I[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]SNUMBER(MA[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]TCH(LEFT(P[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]roductIDCo[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]lumn&"####[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]#",3),{"31[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]111"},0)))[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]+SUMPRODUC[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]T(Expendit[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]ureColumn,[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]--ISNUMBER[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot](MATCH(Yea[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]rColumn,{2[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]019},0)),-[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]-(Departme[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]ntColumn="[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]Ophthalmol[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]ogist"),--[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot](ISNUMBER([/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]SEARCH(","[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]&ProductID[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]Column&","[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot],",22015,2[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot]2080,22170[/FONT]<wbr style="font-size: 16px; font-family: "Open Sans", sans-serif;">[FONT=&quot],"))))[/FONT]

[FONT=&quot]in this above formula. it basically says that Return SUM for year 2019 and department Ophthalmologist and include all Product IDs starting with digit 3 and exclude only Product 31111 and also add the three Product IDs 22015, 22080 and 22170[/FONT]

[FONT=&quot]This formula works but extremely slow. I am converting this now to a Power pivot with Dax functions.[/FONT]

[FONT=&quot]I need help converting the above-mentioned formula to a Dax formula. any help is greatly appreciated.[/FONT]
 

Forum statistics

Threads
1,078,366
Messages
5,339,770
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top