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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jcloss13
Frequent Visitor

Issue with using a lookup table in calculation.

Good Morning, 

 

I feel like this issue should be easily solved but I can't figure it out. 

 

I have two tables, Table A and Table B.

Table A has the detail record and the number of transactions per location. 

Table B is a lookup table for locations, displaying the FTE Count.

I have a Many to One relationship from Table A to B on Location, like below.

 

Table A               

Location         Transaction Time              

A                     12

A                     9

B                     28

B                     6

 

Table B

Location          FTECount

A                     2

B                     3

 

I am having an issue using the FTE Count as a measure to calculate the time and transaction count per employee. I am able to use the Max() function to get the FTE count for each branch but that does not give me a total or translate over to a scatter plot. Ideally, the relationship would pull over the FTE Count for each locations and then the sum for the total.

 

In a Summary, the output I am getting looks like:

 

Location     Sum Trans Time      FTE Count

A                 21                           2

B                 34                           3

Total           55                            N/A

 

OR

 

Location     Sum Trans Time      sum FTE Count

A                 21                           5

B                 34                           5

Total           55                            5

 

I would expect to see

Location     Sum Trans Time      FTE Count

A                 21                           2

B                 34                           3

Total           55                            5

 

Is there something that I am missing here?

 

Thank you,

Jordan

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

The mistake you are committing is that you are creating a relationshop from Table A to Table B.  There are two ways to go about solving this problem:

  1. Create a master table of all unique Locations from Table A and table B (let's call that new table Table C) [This master table can be create by appending Table A and Table B, removing all column expcet Location and then removing duplicated form the location column].  Then create a relationshop from Table A to Table C and Table B to Table C.  After this a simple SUM measure will work as expected.  Remember to drag location from Table C in your visual
  2. If you do not wish to create Table C as proposed above, you can use the RELATEDTABLE function in Table B (after creating a relationshop from Table A to Table B).  In a calculated column in Table B, enter this formula

 

=SUMX(RELATEDTABLE(location_time),location_time[Transaction time])

 

Now create your visual from Table B.

 

Untitled.png


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

View solution in original post

6 REPLIES 6
v-chuncz-msft
Community Support
Community Support

@jcloss13,

 

Given the sample data, you just need to drag Location from Table B not Table A.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
joell001213
Helper II
Helper II

사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰 #아밤ψ
>조엘0013<

사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤

사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

Ashish_Mathur
Super User
Super User

Hi,

 

The mistake you are committing is that you are creating a relationshop from Table A to Table B.  There are two ways to go about solving this problem:

  1. Create a master table of all unique Locations from Table A and table B (let's call that new table Table C) [This master table can be create by appending Table A and Table B, removing all column expcet Location and then removing duplicated form the location column].  Then create a relationshop from Table A to Table C and Table B to Table C.  After this a simple SUM measure will work as expected.  Remember to drag location from Table C in your visual
  2. If you do not wish to create Table C as proposed above, you can use the RELATEDTABLE function in Table B (after creating a relationshop from Table A to Table B).  In a calculated column in Table B, enter this formula

 

=SUMX(RELATEDTABLE(location_time),location_time[Transaction time])

 

Now create your visual from Table B.

 

Untitled.png


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

Thank you for responding. Reading through this I realized I was making an absurd mistake that I have done correctly a hundred times. The Location Table already was the lookup and the value just had to be grabbed from here.

 

Thanks so much for responding!

 

Thank you,

Jordan

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
CahabaData
Memorable Member
Memorable Member

to clarify where you say: the output I am getting looks like

 

you are getting "N/A" in what type of visual?  a table or matrix visual?     at the table level is this field modeled to be a number type or is it a text type?

www.CahabaData.com

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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