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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to achieve this: Multi-Rows with same unique ID

Hi

I've two data sources. Each data source has similar as well as different columns. Below is an extract of all columns.

 

The first table contains all transactions (TX Table)

Ref#DateQuarterMonthRevenue ($)
30243763-1402021-10-062022-Q12022-M01$21,896
30243763-1402021-11-052022-Q12022-M02-$51,227
30243666-1002021-10-062022-Q12022-M01$15,896
30243666-1002021-11-052022-Q12022-M02-$5,122
30987666-2402021-10-062022-Q12022-M01$32,100
30987666-2402021-11-052022-Q12022-M02-$1,227
30243789-902021-10-062022-Q12022-M01$8,348
30243789-902021-11-052022-Q12022-M02-$2,500
39243763-402021-10-062022-Q12022-M01$7,800
38243688-102021-11-052022-Q12022-M02$4,500
37987546-602021-10-062022-Q12022-M01$3,200
36243789-102021-11-052022-Q12022-M02$6,800

 

The second table, contains transactions that are eligible for bonus (Bonus Table)

Ref#QuarterMonthEligible amount for Bonus ($)
30243763-1402022-Q12022-M03$14,600.00
30243666-1002022-Q12022-M01$10,500.00
30987666-2402022-Q12022-M01$30,250.00
30243789-902022-Q12022-M01$6,200.00
30243789-902022-Q12022-M02$6,000.00
39243763-402022-Q12022-M01$4,000.00
38243688-102022-Q12022-M02$4,200.00
37987546-602022-Q12022-M01$2,800.00
36243789-102022-Q12022-M02$6,300.00

 

 

What I would like to see in my visualization is the first 4 columns of TX table + Last column of Bonus Table showing similar to this:

 

Ref#DateQuarterMonthEligible amount for Bonus ($)
30243763-1402021-10-062022-Q12022-M01$14,600.00
30243763-1402021-11-052022-Q12022-M02$0.00
30243666-1002021-10-062022-Q12022-M01$10,500.00
30243666-1002021-11-052022-Q12022-M02$0.00
30987666-2402021-10-062022-Q12022-M01$30,250.00
30987666-2402021-11-052022-Q12022-M02$0.00
30243789-902021-10-062022-Q12022-M01$6,200.00
30243789-902021-11-052022-Q12022-M02$6,000.00
39243763-402021-10-062022-Q12022-M01$4,000.00
38243688-102021-11-052022-Q12022-M02$4,200.00
37987546-602021-10-062022-Q12022-M01$2,800.00
36243789-102021-11-052022-Q12022-M02$6,300.00

 

NOTE: In TX Table, Ref# value can repeat. But in Bonus table, it doesn't repeat.

So, what I want is the first occuanrace of the Ref# in the new table to have the value of Eligible Amount for Bonus and the second occurance to have 0.

 

I tried to use SummarizeColumn but this would repeat the same value for each line for "Eligible amount for Bonus ($)" (when Ref# is repeated).

 

Anythoughts?

 

1 ACCEPTED SOLUTION
MarkLaf
Solution Sage
Solution Sage

First, some inconsistencies that may need clarification.

  1. You do actually have repeat refs in the provided example bonus table (e.g. 30243789-90). 
  2. Your output table seems to be inconsistently pushing bonuses to first date (e.g. pushing M03 bonus to M01 for 30243763-140, but keeping M02 bonus in M02 row for 30243789-90). Is the output table incorrect, or does more of the logic need to be articulated on why we are summarizing at earliest date row for some refs but not others?

With that said, I came up with two measures (for w/o totals and w/ totals) that should work assuming the following:

  1. You want everything summarized on every Ref#'s earliest date row
  2. There is no relationship set up between the tables.
    Note, if repeat Ref# are in both tables, to set up a relationship, we would either need to create a relationship on a calculated column added to each table that factors in Ref# + Date or Month, M:M relationship (with I assume filtering direction from TX Table --> Bonus Table), or a dinstict Ref# table in between the two so we get M:1:M relationship

I've provided two measures, almost identical, except one has an outside SUMX to enable subtotals/totals:

Eligible amount for Bonus ($) = 
VAR CurDate = SELECTEDVALUE( 'TX Table'[Date] )
VAR CurRef = SELECTEDVALUE( 'TX Table'[Ref#] )
VAR RefRankTable = 
CALCULATETABLE( 
    VALUES( 'TX Table'[Date] ), 
    REMOVEFILTERS('TX Table' ), 
    VALUES('TX Table'[Ref#] ) 
)
VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC )
VAR Amount = 
IF( 
    RefRank = 1, 
    CALCULATE( 
        SUM('Bonus Table'[Eligible amount for Bonus ($)]), 
        TREATAS( { CurRef },'Bonus Table'[Ref#] ) 
    ),
    0 
)
RETURN
Amount


Eligible amount for Bonus ($) w/ Total = 
SUMX(
    'TX Table',
    VAR CurDate = 'TX Table'[Date] 
    VAR CurRef = 'TX Table'[Ref#] 
    VAR RefRankTable = 
    CALCULATETABLE( 
        VALUES( 'TX Table'[Date] ), 
        REMOVEFILTERS('TX Table' ), 
        VALUES('TX Table'[Ref#] ) 
    )
    VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC )
    VAR Amount = 
    IF( 
        RefRank = 1, 
        CALCULATE( 
            SUM('Bonus Table'[Eligible amount for Bonus ($)]), 
            TREATAS( { CurRef },'Bonus Table'[Ref#] ) 
        ), 
        0 
    )
    RETURN
    Amount
)

 Output:

MarkLaf_0-1645402303116.png

 

View solution in original post

4 REPLIES 4
MarkLaf
Solution Sage
Solution Sage

First, some inconsistencies that may need clarification.

  1. You do actually have repeat refs in the provided example bonus table (e.g. 30243789-90). 
  2. Your output table seems to be inconsistently pushing bonuses to first date (e.g. pushing M03 bonus to M01 for 30243763-140, but keeping M02 bonus in M02 row for 30243789-90). Is the output table incorrect, or does more of the logic need to be articulated on why we are summarizing at earliest date row for some refs but not others?

With that said, I came up with two measures (for w/o totals and w/ totals) that should work assuming the following:

  1. You want everything summarized on every Ref#'s earliest date row
  2. There is no relationship set up between the tables.
    Note, if repeat Ref# are in both tables, to set up a relationship, we would either need to create a relationship on a calculated column added to each table that factors in Ref# + Date or Month, M:M relationship (with I assume filtering direction from TX Table --> Bonus Table), or a dinstict Ref# table in between the two so we get M:1:M relationship

I've provided two measures, almost identical, except one has an outside SUMX to enable subtotals/totals:

Eligible amount for Bonus ($) = 
VAR CurDate = SELECTEDVALUE( 'TX Table'[Date] )
VAR CurRef = SELECTEDVALUE( 'TX Table'[Ref#] )
VAR RefRankTable = 
CALCULATETABLE( 
    VALUES( 'TX Table'[Date] ), 
    REMOVEFILTERS('TX Table' ), 
    VALUES('TX Table'[Ref#] ) 
)
VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC )
VAR Amount = 
IF( 
    RefRank = 1, 
    CALCULATE( 
        SUM('Bonus Table'[Eligible amount for Bonus ($)]), 
        TREATAS( { CurRef },'Bonus Table'[Ref#] ) 
    ),
    0 
)
RETURN
Amount


Eligible amount for Bonus ($) w/ Total = 
SUMX(
    'TX Table',
    VAR CurDate = 'TX Table'[Date] 
    VAR CurRef = 'TX Table'[Ref#] 
    VAR RefRankTable = 
    CALCULATETABLE( 
        VALUES( 'TX Table'[Date] ), 
        REMOVEFILTERS('TX Table' ), 
        VALUES('TX Table'[Ref#] ) 
    )
    VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC )
    VAR Amount = 
    IF( 
        RefRank = 1, 
        CALCULATE( 
            SUM('Bonus Table'[Eligible amount for Bonus ($)]), 
            TREATAS( { CurRef },'Bonus Table'[Ref#] ) 
        ), 
        0 
    )
    RETURN
    Amount
)

 Output:

MarkLaf_0-1645402303116.png

 

Anonymous
Not applicable

Thanks @MarkLaf 

The second Measure worked perfectly fine. Only the total is not showing properly.

But the logic is what I asked for.

 

YJAMOUS_0-1645409981971.png

 

Thanks for pointing out the issue with the total. When Ref# wasn't filtered, it was only summing bonuses for earliest (tied) date for all the selected Ref#'s. This measure is working better with totals, at least with the sample data provided:

Eligible amount for Bonus ($) w/ Total_fixedtotals = 
VAR RefTab =
GENERATE(
    SUMMARIZE( 'TX Table' ,'TX Table'[Ref#],'TX Table'[Month]),
    VAR RefRankTable = 
    CALCULATETABLE( 
        VALUES( 'TX Table'[Month] ), 
        REMOVEFILTERS('TX Table' ), 
        TREATAS( { 'TX Table'[Ref#] },'TX Table'[Ref#] )
    )
    VAR RefRank = RANKX( RefRankTable, 'TX Table'[Month], , ASC )
    RETURN
    ROW( "Rank", RefRank)
)
RETURN
CALCULATE( 
    SUM('Bonus Table'[Eligible amount for Bonus ($)]) + 0,
    TREATAS( 
        CALCULATETABLE( VALUES('TX Table'[Ref#] ), FILTER( RefTab, [Rank] = 1 ) ),
        'Bonus Table'[Ref#] 
    ) 
)

 

Anonymous
Not applicable

Thanks @MarkLaf 

Yessssssss. Working like a charm.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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