Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 oth...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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! ðŸ˜€

MFelix's avatar
MFelix
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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! ðŸ˜€