Excel formula to DAX

Question

HiDemand-ID&nbsp;DemandSupplyOS FlagUnfulfilledTotal SupplySupply Zero/Non-ZeroTotal UnfulfilledUnfulfilled Zero/Non-Zero1&nbsp;42N24Non-Zero0Zero2&nbsp;107N39Non-Zero1Non-Zero3&nbsp;60N60Zero6Non-Zero1&nbsp;&nbsp;2Y-2DNCDNCDNCDNC2&nbsp;&nbsp;2Y-2DNCDNCDNCDNC3&nbsp;&nbsp;0Y0DNCDNCDNCDNC&nbsp;in the above mentioned table for total supply we are using a formula&nbsp;=IF(E2="N", D2+XLOOKUP(A2,$A$2:$A$7,$D$2:$D$7,"NA",0,-1),"DNC")&nbsp;Can anyone suggest a respective dax for the above excel formula.Thank you,

ahmedx · Answer

Share some data to work with (in a format the can be pasted in an MS Excel file) and show the expected result.

kesavaraman · Answer

Hi&nbsp;Row no.Demand-ID&nbsp;DemandSupplyOS FlagUnfulfilledTotal SupplySupply Zero/Non-ZeroTotal UnfulfilledUnfulfilled Zero/Non-Zero1200027_MS-106365_202230&nbsp;0.050.05N02.05Non-Zero-2Non-Zero1300027_MS-106365_288764&nbsp;0.10N0.12Non-Zero-1.9Non-Zero1400014_MS-106365_305428&nbsp;42N22Non-Zero2Non-Zero1500027_MS-106365_202230&nbsp;&nbsp;2Y-2DNCDNCDNCDNC1600027_MS-106365_288764&nbsp;&nbsp;2Y-2DNCDNCDNCDNC1700014_MS-106365_305428&nbsp;&nbsp;0Y0DNCDNCDNCDNC&nbsp;&nbsp;Here the formula&nbsp; used for total supply is&nbsp;=IF(F12="N", E12+XLOOKUP(B12,$B$12:$B$17,$E$12:$E$17,"NA",0,-1),"DNC")&nbsp;and I need to import this total supply column in powerbi so I need a suitable dax&nbsp;

ahmedx · Answer

post the excel file, your formula is so unclearIF(E2="N"IF(F12="N"

kesavaraman · Answer

HiRow no.Demand-ID&nbsp;DemandSupplyOS FlagUnfulfilledTotal SupplySupply Zero/Non-Zero1200027_MS-106365_202230&nbsp;0.050.05N=D21-E21=IF(F21="N", E21+XLOOKUP(B21,$B$12:$B$17,$E$12:$E$17,"NA",0,-1),"DNC")=IFS(H21=0,"Zero",H21="DNC",H21,TRUE,"Non-Zero")1300027_MS-106365_288764&nbsp;0.10N=D22-E22=IF(F22="N", E22+XLOOKUP(B22,$B$12:$B$17,$E$12:$E$17,"NA",0,-1),"DNC")=IFS(H22=0,"Zero",H22="DNC",H22,TRUE,"Non-Zero")1400014_MS-106365_305428&nbsp;42N=D23-E23=IF(F23="N", E23+XLOOKUP(B23,$B$12:$B$17,$E$12:$E$17,"NA",0,-1),"DNC")=IFS(H23=0,"Zero",H23="DNC",H23,TRUE,"Non-Zero")1500027_MS-106365_202230&nbsp;&nbsp;2Y=D24-E24=IF(F24="N", E24+XLOOKUP(B24,$B$2:$B$7,$E$2:$E$7,"NA",0,-1),"DNC")=IFS(H24=0,"Zero",H24="DNC",H24,TRUE,"Non-Zero")1600027_MS-106365_288764&nbsp;&nbsp;2Y=D25-E25=IF(F25="N", E25+XLOOKUP(B25,$B$2:$B$7,$E$2:$E$7,"NA",0,-1),"DNC")=IFS(H25=0,"Zero",H25="DNC",H25,TRUE,"Non-Zero")1700014_MS-106365_305428&nbsp;&nbsp;0Y=D26-E26=IF(F26="N", E26+XLOOKUP(B26,$B$2:$B$7,$E$2:$E$7,"NA",0,-1),"DNC")=IFS(H26=0,"Zero",H26="DNC",H26,TRUE,"Non-Zero")&nbsp;&nbsp;Row no.Demand-ID&nbsp;DemandSupplyOS FlagUnfulfilledTotal SupplySupply Zero/Non-Zero1200027_MS-106365_202230&nbsp;0.050.05N02.05Non-Zero1300027_MS-106365_288764&nbsp;0.10N0.12Non-Zero1400014_MS-106365_305428&nbsp;42N22Non-Zero1500027_MS-106365_202230&nbsp;&nbsp;2Y-2DNCDNC1600027_MS-106365_288764&nbsp;&nbsp;2Y-2DNCDNC1700014_MS-106365_305428&nbsp;&nbsp;0Y0DNCDNC

ahmedx · Answer

can you explain, F21 is the OS Flag column?and $B$12:$B$17 is which column?and$E$12:$E$17 is which column?

Forum Discussion

Excel formula to DAX

6 Replies

Related content

DAX Formula Help

Conversion of Excel formulas to DAX within Power BI.

DAX Formula

DAX Formula

Dax Formula correction help

Demand-ID	Demand	Supply	OS Flag	Unfulfilled	Total Supply	Supply Zero/Non-Zero	Total Unfulfilled	Unfulfilled Zero/Non-Zero
1	4	2	N	2	4	Non-Zero	0	Zero
2	10	7	N	3	9	Non-Zero	1	Non-Zero
3	6	0	N	6	0	Zero	6	Non-Zero
1		2	Y	-2	DNC	DNC	DNC	DNC
2		2	Y	-2	DNC	DNC	DNC	DNC
3		0	Y	0	DNC	DNC	DNC	DNC

Row no.	Demand-ID	Demand	Supply	OS Flag	Unfulfilled	Total Supply	Supply Zero/Non-Zero	Total Unfulfilled	Unfulfilled Zero/Non-Zero
12	00027_MS-106365_202230	0.05	0.05	N	0	2.05	Non-Zero	-2	Non-Zero
13	00027_MS-106365_288764	0.1	0	N	0.1	2	Non-Zero	-1.9	Non-Zero
14	00014_MS-106365_305428	4	2	N	2	2	Non-Zero	2	Non-Zero
15	00027_MS-106365_202230		2	Y	-2	DNC	DNC	DNC	DNC
16	00027_MS-106365_288764		2	Y	-2	DNC	DNC	DNC	DNC
17	00014_MS-106365_305428		0	Y	0	DNC	DNC	DNC	DNC

Row no.	Demand-ID	Demand	Supply	OS Flag	Unfulfilled	Total Supply	Supply Zero/Non-Zero
12	00027_MS-106365_202230	0.05	0.05	N	=D21-E21	=IF(F21="N", E21+XLOOKUP(B21,$B$12:$B$17,$E$12:$E$17,"NA",0,-1),"DNC")	=IFS(H21=0,"Zero",H21="DNC",H21,TRUE,"Non-Zero")
13	00027_MS-106365_288764	0.1	0	N	=D22-E22	=IF(F22="N", E22+XLOOKUP(B22,$B$12:$B$17,$E$12:$E$17,"NA",0,-1),"DNC")	=IFS(H22=0,"Zero",H22="DNC",H22,TRUE,"Non-Zero")
14	00014_MS-106365_305428	4	2	N	=D23-E23	=IF(F23="N", E23+XLOOKUP(B23,$B$12:$B$17,$E$12:$E$17,"NA",0,-1),"DNC")	=IFS(H23=0,"Zero",H23="DNC",H23,TRUE,"Non-Zero")
15	00027_MS-106365_202230		2	Y	=D24-E24	=IF(F24="N", E24+XLOOKUP(B24,$B$2:$B$7,$E$2:$E$7,"NA",0,-1),"DNC")	=IFS(H24=0,"Zero",H24="DNC",H24,TRUE,"Non-Zero")
16	00027_MS-106365_288764		2	Y	=D25-E25	=IF(F25="N", E25+XLOOKUP(B25,$B$2:$B$7,$E$2:$E$7,"NA",0,-1),"DNC")	=IFS(H25=0,"Zero",H25="DNC",H25,TRUE,"Non-Zero")
17	00014_MS-106365_305428		0	Y	=D26-E26	=IF(F26="N", E26+XLOOKUP(B26,$B$2:$B$7,$E$2:$E$7,"NA",0,-1),"DNC")	=IFS(H26=0,"Zero",H26="DNC",H26,TRUE,"Non-Zero")