cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickProp28
Post Partisan
Post Partisan

take a long runtime for vLookup from another table

Dear Community,

 

I have 2 table. Table A have 2 column (ID and jobnumber)

Table B is the details of ID. I created a new column (Count Job) in table B  to count how many jobnumber inside the ID. 

DAX I used, 
Count Job = CALCULATE(DISTINCTCOUNT(TableA[JobNumber]),FILTER(TableA,TableA[ID]=Table[ID]))

Example (Table A)

NickProp28_0-1629860629481.png

 

Table B 

NickProp28_1-1629860649318.png

Since I have huge data, the runtime for this DAX is more than 3 hours. 
So would like to ask is any other way to get the same solution.

Thanks for your attention. Any help provided will be greatly appreciated.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@NickProp28 , Duplicate Table A in Power Query and keep ID and JobNumber

Remove duplicates

Group By -> ID and JobNumber count  - https://docs.microsoft.com/en-us/power-query/group-by

 

Then merge this with table B

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@NickProp28 , Duplicate Table A in Power Query and keep ID and JobNumber

Remove duplicates

Group By -> ID and JobNumber count  - https://docs.microsoft.com/en-us/power-query/group-by

 

Then merge this with table B

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!