Relating One Column to Two Columns in Another Table while using Both Relationships in a Measure

[Anonymous]

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!

[MFelix]

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.

[Anonymous]

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!

[MFelix]

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.

[Anonymous]

Hi @MFelix! Apologies for the getting back to you late.

---

@MFelix wrote:

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.

---

Unfortunately, that won't work as well since there are rows in TRAN that actually use the ACCT[ID] and not the employee number for Emp Cards. See ACCT[ID] = 5 in the sample tables in my earlier reply. 

Thank you for the measure! Unfortunately it behaved similar to the original measure and was unable to incorporate the TRAN rows with employee number (e.g. the result is the same as the table in my earlier reply where 3000 was missing) when I used it in my actual PBIX file. I think it's because of the crossfiltering direction between ACCT and TRAN. The file you gave me had a single direction between these two tables whereas my actual table direction is at set to "Both" (kindly see sample tables in my earlier reply).

It did point me to a new way of thinking, however, and led me to this measure which has so far been correct:

IF (
    ISCROSSFILTERED(ACCT[Employee_Number]),
   
        SUM(TRAN[Total_Tran_Count])
        + CALCULATE (
            SUM(TRAN[Total_Tran_Count]),
            USERELATIONSHIP(CALENDAR[DateKey], TRAN[TranDate_DateKey]),
            USERELATIONSHIP(ACCT[Employee_Number], TRAN[ID])

    ),
    CALCULATE(SUM(TRAN[Total_Tran_Count]))
)

This has so far given me the correct number for both the TRAN[Product_Type] totals and the column total, AND has kept those filters even when I slice by TRAN[Product_Type]. I also didn't want to depend on exact values of TRAN[Product_Type] since it may change in the future.

Please let me know if this can be improved and/or if there might be problems with this measure.

Thanks!

[MFelix]

Hi @Anonymous ,

Regarding the relationship that you present I was not abble to replicate them in my model because I got an many to many relationship when you refer that is a one to many.

The suggestion I was giving was in order to create a single ID column, in my opinion the crossfilter relationships can gives headaches in the future especially if you need to make other calculations and you can have to create additional filterings or measure because the filter are for both sides of the table.

The measure seems fine to me and does not appear to have any major issues, but be carefull because depending on the size of your model and further information you can have performance issues or the need to setup additonal measures for calculations that would be simple.

Please don't forget to mark you answer as correct so it can help others.

[Anonymous · Accepted solution]

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! 😀