Forum Discussion

mmiklauz1's avatar
mmiklauz1
Regular Visitor
1 year ago
Solved

Extra rows vs second table

Hi everyone,  I'm struggling a few days whit this problem so I would appreciate your help. I need create a dynamic measure to show only the "extra rows" i each table: so for example I have two source...
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hi mmiklauz1,
    Thank you for reaching out to the Microsoft Fabric Forum Community. I’ve gone ahead and reproduced your scenario using Power Query.

    Below is the M code I used in Power BI to reproduce your scenario:

    let

    Table1 = #table({"Value"}, {{5},{5},{5},{5},{4},{3},{2},{1}}),

    Table2 = #table({"Value"}, {{5},{3},{2},{1}}),


    Table1Grouped = Table.Group(Table1, {"Value"}, {{"Count1", each Table.RowCount(_), Int64.Type}}),

    Table2Grouped = Table.Group(Table2, {"Value"}, {{"Count2", each Table.RowCount(_), Int64.Type}}),


    Merged = Table.NestedJoin(Table1Grouped, {"Value"}, Table2Grouped, {"Value"}, "T2", JoinKind.LeftOuter),

    Expanded = Table.ExpandTableColumn(Merged, "T2", {"Count2"}),



    WithReplacedNulls = Table.ReplaceValue(Expanded, null, 0, Replacer.ReplaceValue, {"Count2"}),


    WithExtra = Table.AddColumn(WithReplacedNulls, "Extra", each List.Max({[Count1] - [Count2], 0}), Int64.Type),



    Filtered = Table.SelectRows(WithExtra, each [Extra] > 0),


    WithRepeated = Table.AddColumn(Filtered, "Repeated", each List.Repeat({[Value]}, [Extra])),


    ExpandedRows = Table.ExpandListColumn(WithRepeated, "Repeated"),



    FinalOutput = Table.SelectColumns(ExpandedRows, {"Repeated"}),

    Renamed = Table.RenameColumns(FinalOutput, {{"Repeated", "Value"}})

in

    Renamed



    I’m also attaching the output screenshot and .pbix file here for your reference so you can explore it end-to-end:

     

    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi mmiklauz1 ,

 

You can solve this by creating a separate AmountList table to act as a common axis between the two tables and then calculating the count difference dynamically in measures. Start by creating a standalone table that captures all distinct Amount values from both Table1 and Table2. This can be done with the following DAX formula:

AmountList = DISTINCT(UNION(SELECTCOLUMNS(Table1, "Amount", Table1[Amount]), SELECTCOLUMNS(Table2, "Amount", Table2[Amount])))

Next, create two measures to count the occurrences of each Amount value in both tables. For Table1, use:

Count_Table1 = 
CALCULATE(
    COUNTROWS(Table1),
    FILTER(Table1, Table1[Amount] = SELECTEDVALUE(AmountList[Amount]))
)

For Table2, use:

Count_Table2 = 
CALCULATE(
    COUNTROWS(Table2),
    FILTER(Table2, Table2[Amount] = SELECTEDVALUE(AmountList[Amount]))
)

To calculate the extra rows in Table1 compared to Table2, define the following measure:

Extra_Table1 = 
VAR Count1 = [Count_Table1]
VAR Count2 = [Count_Table2]
RETURN IF(Count1 > Count2, Count1 - Count2, BLANK())

This measure returns the difference only when Table1 has more rows than Table2 for a given Amount. Use AmountList[Amount] as the row in a matrix visual and add Extra_Table1 as the value. The result will dynamically show only the "extra rows" from Table1 based on the filter context. If needed, you can apply the same logic in reverse to get extra rows from Table2.

 

Best regards,

 

mmiklauz21's avatar
mmiklauz21
New Member
1 year ago

Great DataNjinja777, your solution almost solve my problem, but as you can see for example

the amount of 3.4 appears 2 times in the first table and does not appear in the second, so the amount appears 2X more in the first table than in the second table and then it should appear 2 times. Here are more examples:

  • first table 3,3,3,3,2,1
  • second table 3,2,1
  • result 3,3,3 - three amounts more than in the second table 

 

I would be grateful if you could solve this