Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX function to check if record exists in multiple files

Hi everyone! Could you please advise me on below scenario

I get 2 source files (Excel) named HeadCount and Terminations every week and I combine latest 6 of each of these files and load them to PowerBI. Both the files have a load date column which differentiates the data from week to week.

I use load date column from HeadCount file to count the number of employees each week and termination date column from Terminations file to count nuumber of terminations in a week.

Now I have a requirement to calculate the count of employees that exists in prior week AND doesn't exists in current week AND doesn't exist in Terminations file.

 

Here is the link to my sample datasets:

https://1drv.ms/f/s!AtqcKHVDSd3QghmFZSDgcfcVhoOJ

 

My snapshot of data below

 

HC.PNG                                                           Terms.PNG

Consider 2/2/2019 is current week and 1/26/2019 is prior week. My condition is emp id  (EXISTS IN PRIOR WEEK AND DOESNT EXIST IN CURRENT WEEK) to be checked from HeadCount file AND (DOESNT EXIST IN TERMINATIONS) to be checked from Terminations file. Now, If you look at my HeadCount data, Emp ID s 4,5,6 exists in prior week(1/26) and doesnt exists in current week (2/2). Then comparing it with Terminations file for current week (2/2), Emp IDs 5 and 6 doesnt exist in Terminations. 

So my result should be 2 which is count of employee ids that satisfies the above three conditions.

 

Any ideas how this can be implemented in DAX? Or any other ideas how this can be achieved?

Hoping for a solution here in this forum

Greatly appreciate your response and efforts! 

 

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

Share sample datasets and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur Hi, I edited my original post to include link for sample datasets and snapshots of my data for a quick view. 

Thank you!

Hi,

 

Please download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_MathurThank you so very much for your efforts. Unfortunately, I am not supposed to use any filters on the week. Is there a way we can get the count without using slicer on the week number?

 

Hi,

 

You are welcome.  If you cannot use a filter/slicer for selecting the week, then how will you define the current or previous week?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur,

Current week and Prior week will be based on the Load Date. Like

Current week: WEEKNUM(Headcount[LoadDate],2)=WEEKNUM(TODAY(),2)

Prior week:      WEEKNUM(Headcount[LoadDate],2)=WEEKNUM(TODAY(),2)-1

We will have to find a solution that incorporates the date filters in the DAX logic. 

 

This is how I combine 6 weeks headcount data to a single file and extract load date from file name and pull into power BI, if this helps. The same is for Terminations file too.

 

let
Source = SharePoint.Files("Sharepoint Path", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Headcount FTE as of")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",6),

#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4", "Name.5", "Name.6", "Name.7"}),


#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Name.7", each Text.BeforeDelimiter(_, "."), type text}}),

#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type text}}, "en-US"),{"Month", "Date"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Merged", "Year"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),

#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Excel.Workbook([Content])),

#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Merged.1", "LoadDate"}}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns1", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each Text.Contains([Custom.Name], "Headcount FTE Details")),

#"Expanded FormatHeader" = Table.ExpandTableColumn(#"Removed Columns4", "FormatHeader", {"Column Header1, Column Header2, ........................................"})

in
#"Expanded FormatHeader"

Hi,

 

I'll take quite some time to solve this.  It's better if someone else helps you with this.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur No problem. Thanks for your time. Let me know if you find a solution anytime. 'Coz I don't even think Im close to it 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.