Excel Power Pivot DAX function Multiple Sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1392390%22%20slang%3D%22en-US%22%3EExcel%20Power%20Pivot%20DAX%20function%20Multiple%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1392390%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%3CBR%20%2F%3EI%20have%20a%20problem%20with%20DAX%20function%20in%20power%20pivot.%3CBR%20%2F%3EI%20have%20loaded%202%20tables%20in%202%20different%20sheets%20in%20the%20power%20pivot.%3CBR%20%2F%3EI%20need%20to%20write%20a%20function%20in%20table1%20BadgeRecords%20which%20make%20a%20comparison%20with%20data%20located%20in%20table2%20LeaveReq.%3CBR%20%2F%3EI%20will%20need%20to%20use%20a%20nested%20if%20I%20think%2C%20which%20sintax%20in%20Dax%20should%20be%20like%20the%20following%3A%3C%2FP%3E%3CP%3E%3DIF(condition1%2Cresult1%2CIF(condition2%2Cresult2%2Cresult3))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20function%20added%20in%20an%20extra%20column%20in%20BadgeRecord%20sheet%20looks%20like%20this%20but%20returns%20an%20error%20that's%20caused%20by%20the%20LeaveReq%5BFROM%20DATE%5D%20field%20located%20in%20table%202%20and%20I'm%20not%20getting%20why%2C%20I'm%20not%20sure%20I'm%20missing%20something.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dif(BadgeRecords_bk%5BLeave%5D%3D%20FALSE()%3B%200%3B%20IF(BadgeRecords_bk%5BDate%5D%3DLeaveReq%5BFROM%20DATE%5D%3B1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestion%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConsider%20I%20have%20BadgeRecords%20table%20which%20records%20entry%20and%20exit%20date%20and%20time%20of%20each%20employee%2C%3CBR%20%2F%3Ethe%20other%20table%20LeaveReq%20it's%20a%20list%20of%20Holiday%20requests%20from%20each%20employee.%3CBR%20%2F%3EIn%20order%20not%20to%20record%20a%20late%20arrival%20if%20an%20employee%20took%20holiday%20or%20permit%20leave%20that%20day%20I%20should%20consider%20also%20that.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESo%20First%20IF%20condition%20says%20true%20or%20false%20if%20Employee%20Name%20in%20first%20table%20is%20found%20in%20the%20Leave%20Request%20table.%3CBR%20%2F%3EIf%20it%20founds%20it%20then%20it%20should%20check%20the%20date%20of%20the%20holiday%20request%20in%20the%20secod%20table%20if%20it%20matches%20the%20one%20in%20the%20first%20table%2C%20if%20the%20date%20it's%20the%20same%20it%20should%20not%20consider%20he%20was%20late%20that%20day%20cause%20he%20took%20a%20permit.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESo%20firstly%20it%20goes%20i%20the%20second%20table%20to%20check%20employee%20code%20if%20took%20any%20permit%20request%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Marcio86_1-1589536493501.png%22%20style%3D%22width%3A%20727px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F192013i5051ADBD644C99C2%2Fimage-dimensions%2F727x349%3Fv%3D1.0%22%20width%3D%22727%22%20height%3D%22349%22%20title%3D%22Marcio86_1-1589536493501.png%22%20alt%3D%22Marcio86_1-1589536493501.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20first%20condition%20is%20true%20When%20finds%20the%20first%20record%20check%20if%20the%20date%20in%20LeaveReq%20table%20is%20the%20same%20of%20the%20on%20in%20BadgeRecord%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Marcio86_2-1589536741044.png%22%20style%3D%22width%3A%20716px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F192014i32C8F7425BAF23E5%2Fimage-dimensions%2F716x340%3Fv%3D1.0%22%20width%3D%22716%22%20height%3D%22340%22%20title%3D%22Marcio86_2-1589536741044.png%22%20alt%3D%22Marcio86_2-1589536741044.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELeave%20Req%20Table%20looks%20like%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Marcio86_3-1589536915052.png%22%20style%3D%22width%3A%20694px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F192015i962EBEE80DF85442%2Fimage-dimensions%2F694x381%3Fv%3D1.0%22%20width%3D%22694%22%20height%3D%22381%22%20title%3D%22Marcio86_3-1589536915052.png%22%20alt%3D%22Marcio86_3-1589536915052.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1392390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1393814%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Pivot%20DAX%20function%20Multiple%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1393814%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668976%22%20target%3D%22_blank%22%3E%40Marcio86%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20not%20enough%20information%2C%20but%20perhaps%20you%20don't%20have%201%20to%20many%20relationship%20between%20Leave%20Req%20and%26nbsp%3B%3CSPAN%3EBadgeRecords%2C%20with%20it%20use%20RELATED%20in%20formula.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi there!
I have a problem with DAX function in power pivot.
I have loaded 2 tables in 2 different sheets in the power pivot.
I need to write a function in table1 BadgeRecords which make a comparison with data located in table2 LeaveReq.
I will need to use a nested if I think, which sintax in Dax should be like the following:

=IF(condition1,result1,IF(condition2,result2,result3))

 

My function added in an extra column in BadgeRecord sheet looks like this but returns an error that's caused by the LeaveReq[FROM DATE] field located in table 2 and I'm not getting why, I'm not sure I'm missing something.

 

=if(BadgeRecords_bk[Leave]= FALSE(); 0; IF(BadgeRecords_bk[Date]=LeaveReq[FROM DATE];1))

 

Any suggestion?

 

Consider I have BadgeRecords table which records entry and exit date and time of each employee,
the other table LeaveReq it's a list of Holiday requests from each employee.
In order not to record a late arrival if an employee took holiday or permit leave that day I should consider also that.


So First IF condition says true or false if Employee Name in first table is found in the Leave Request table.
If it founds it then it should check the date of the holiday request in the secod table if it matches the one in the first table, if the date it's the same it should not consider he was late that day cause he took a permit.


So firstly it goes i the second table to check employee code if took any permit request 

 

Marcio86_1-1589536493501.png

 

If first condition is true When finds the first record check if the date in LeaveReq table is the same of the on in BadgeRecord

 

Marcio86_2-1589536741044.png

 

Leave Req Table looks like this

 

Marcio86_3-1589536915052.png

 

 

 

 

 

 

 

 

1 Reply
Highlighted

@Marcio86 

That's not enough information, but perhaps you don't have 1 to many relationship between Leave Req and BadgeRecords, with it use RELATED in formula.