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 ,

 

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