Forum Discussion
Relating One Column to Two Columns in Another Table while using Both Relationships in a Measure
Greetings!
Any help is greatly appreciated! 😀
I. The Data
I have two tables—one contains account information (consider three columns `ID`, `Product_Type`, and `Employee_Number`), and the other contains transactional data (consider two columns `ID`, and `Total_Tran_Count`). Let's the call the former the ACCT table and the latter the TRAN table. Initially, I related the two by the common `ID` column in both (active), and this worked fine. However, I noticed that for `Product_Type` = "Emp Card", in TRAN, the employee number is sometimes used instead of the actual ID.
ACCT TRAN
ID 1:* ID
Thus, when I created the below measure (I also actively related TRAN to a calendar table but I included USERELATIONSHIP just to be safe),
01 Transaction Count =
CALCULATE(SUM(TRAN[Total_Tran_Count]),
USERELATIONSHIP('CALENDAR: Transactions'[DateKey], TRAN[Trandate_DateKey]))
...and used it in say a column chart where Values = 01 Transaction Count and Legend = ACCT[Product_Type], I was getting a smaller number for `Product_Type` = "Emp Card" since there are rows that are actively related to ACCT[ID] but should be related to ACCT[Employee_Number]. Kindly see below simplified sample tables.
| Sample Data for ACCT | Sample Data for TRAN | ||||
| ID | Product_Type | Employee_Number | ID | Total_Tran_Count | |
| 1 | Card A | null | 1 | 50 | |
| 2 | Card B | null | 2 | 100 | |
| 3 | Emp Card | 000001 | 000001 | 25 |
II. Action Steps
1. The first thing I did was create an inactive relationship between ACCT[Employee_Number] and TRAN[ID], and...
2. I adjusted the measure to the following:
01 Transaction Count v2 =
CALCULATE(SUM(TRAN[Total_Tran_Count]),
USERELATIONSHIP('CALENDAR: Transactions'[DateKey], TRAN[Trandate_DateKey]),
USERELATIONSHIP(ACCT[Employee_Number], TRAN[ID]))
...but as mentioned in this link, this defaults to a logical AND, and hence breaks my visuals whenever I use a slicer with `Product_Type` and/or `Product_Type` is set to a legend, except when `Product_Type` = "Emp Card".
3. I then adjusted it again to the following:
01 Transaction Count v3 =
CALCULATE(SUM(TRAN[Total_Tran_count]),
USERELATIONSHIP('CALENDAR: Transactions'[DateKey], TRAN[Trandate_DateKey]),
KEEPFILTERS(ACCT[Product_Type] <> "Emp Card"))
+
CALCULATE(SUM(TRAN[Total_Tran_count]),
USERELATIONSHIP('CALENDAR: Transactions'[DateKey], TRAN[Trandate_DateKey]),
USERELATIONSHIP(ACCT[Employee_Number], TRAN[ID]),
KEEPFILTERS(ACCT[Product_Type] = "Emp Card"))
...and this is the closest I could get in getting the full SUM(TRAN[Total_Tran_count]) for `Product_Type` = "Emp Card" AND being able to slice by product BUT it's still not perfect as I am now getting a smaller overall total since the Emp Cards that were originally included in the very first version of 01 Transaction Count were now being excluded in the first CALCULATE in 01 Transaction v3. Not to mention, I don't think this is a very robust adjustment.
III. Next Steps
Is there any way to achieve what I need without creating a new column under TRAN that looks up a value in ACCT[Employee_Number] and returns the corresponding ACCT[ID]? I'm also open to having reference tables if that helps since I may have other transacctional tables that have a similar problem with Emp Cards.
Sorry for the long post, and thank you very much!
Hello!
I tried to apply the measure I got last week to my actual PBIX file and data model to stress test it since there are a lot of dimensions available to the end users to filter the total transaction count by. It took longer than I expected as I found some edge cases that needed to be within the scope of the measure.
Other issues I encountered were wrong calculations when aggregating by columns in other, connected tables, or by other columns in TRAN. This was illustrated in a Stacked Bar + Line Combo Chart wherein—using the measure in both "Column Values" and "Line Values"—the column series that also came from TRAN (e.g. TRAN[Transaction_Type])was not able aggregate correctly but the line chart was, which meant the measure had trouble when I was aggregating by anything other than ACCT[Product_Type] and CALENDAR[Date] with say, a table for the former and a line chart for the latter.
In any case, the measure I will be using for now is this:FINAL Transaction Count = VAR first_nonblank = CALCULATE(FIRSTNONBLANK(ACCT[Employee_Number], 1), FILTER(ALLSELECTED(ACCT), ACCT[Employee_Number] <> "")) VAR edge_case = CALCULATE(IF(HASONEVALUE(ACCT[Employee_Number]), TRUE(), FALSE()), ALLSELECTED(ACCT[Employee_Number])) RETURN IF ( ISBLANK(first_nonblank) && NOT(edge_case), -- 1. Result if True IF ( SELECTEDVALUE(ACCT[Employee_Number]) = "" && NOT(edge_case), -- 1.1. CALCULATE ( SUM(TRAN[Total_Tran_Count]), USERELATIONSHIP(CALENDAR[DateKey], TRAN[TranDate_DateKey]), USERELATIONSHIP(ACCT[Employee_Number], TRAN[ID]) ), -- 1.2. SUM(TRAN[Total_Tran_Count]) + CALCULATE ( SUM(TRAN[Total_Tran_Count]), USERELATIONSHIP(CALENDAR[DateKey], TRAN[TranDate_DateKey]), USERELATIONSHIP(ACCT[Employee_Number], TRAN[ID]) ) ), -- 2. Result if False IF ( ISCROSSFILTERED(ACCT), -- 2.1. Result if True IF ( OR( SELECTEDVALUE(ACCT[PRODUCT]) = "Emp Card", AND("Emp Card" IN VALUES(ACCT[Product_Type]), ISFILTERED(ACCT[Product_Type])) ), -- 2.1.1. SUM(TRAN[Total_Tran_Count]) + CALCULATE ( SUM(TRAN[Total_Tran_Count]), USERELATIONSHIP(CALENDAR[DateKey], TRAN[TranDate_DateKey]), USERELATIONSHIP(ACCT[Employee_Number], TRAN[ID]) ), -- 2.1.2. SUM(TRAN[Total_Tran_Count]) ), -- 2.2. Result if False SUM(TRAN[Total_Tran_Count]) ) )Generally, the needed measure is really under "2. Result if False", but the lines before that is for the edge cases and aggregating by other columns. I do think that there must be a simpler method for what I'm trying to achieve, but it escapes me for now. I also did end up relying on the value of ACCT[Product_Type].
I hope this is useful to others, and if there's anyone that has another solution, please don't hesitate to message me! 😀
6 Replies
Hi Anonymous ,
Not really sure if this is what you need since the number of lines in your example is very small but try using the following syntax with disconnected tables:
Measure = CALCULATE ( SUM ( 'TRAN'[Total_Tran_count] ); FILTER ( 'TRAN'; 'TRAN'[ID] = SELECTEDVALUE ( ACCT[ID] ) || 'TRAN'[ID] = SELECTEDVALUE ( ACCT[Employee_Number] ) ) )Be aware that in you sample data you do not have example with values in bopth columns or duplicated values so this may need some changes if there are some columns with both ID and employye number or you will get the sum of all the values.
Hello, MFelix !
Thank you for your response! Unfortunately the suggested measure did not solve the problem and resulted in visuals displaying `BLANK`. I also don't think I can do disconnected tables.
Apologies for the oversimplified sample tables. Kindly see a more informative one below (highlighted rows are Emp Card rows):
As you can see, the current measure fails to include the two rows in TRAN because they make use of employee number instead of the actual ID. Please let me know if I need to change the cross filtering direction, etc.
Thanks!
Hi Anonymous ,
I believe that the better solution is to fill the blanks on the employee ID on the ACCT table with the ID then you make the relationship with that column.
However you can try the following code:
Measure = IF ( ISINSCOPE ( ACCT[Product_Type] ); IF ( SELECTEDVALUE ( ACCT[Product_Type] ) = BLANK (); BLANK (); SWITCH ( SELECTEDVALUE ( ACCT[Product_Type] ); "Emp Card"; SUM ( 'TRAN'[Total_Tran_Count] ) + CALCULATE ( SUM ( 'TRAN'[Total_Tran_Count] ); FILTER ( ALLSELECTED ( ACCT[Product_Type] ); ACCT[Product_Type] = BLANK () ) ); SUM ( 'TRAN'[Total_Tran_Count] ) ) ); SUM ( 'TRAN'[Total_Tran_Count] ) )In this measure I assume that you have emp card everytime there is no ID on the related table.
Check PBIX attach.
