Extra rows vs second table

[mmiklauz1]

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 sources table1 and table2. 

Table1 has rows 5,5,5,5,4,3,2,1 and Table2 has rows 5,3,2,1. Should be dynamic depending on the filter context and the rows displayed in the tables.

Expected Output:
For the Amount 5:

• FirstTable: 4 occurrences
• SecondTable: 1 occurrence

Expected result: Show 3 extra 5s from FirstTable in Table matrix visual

For the Amount 4:

• FirstTable: 1 occurrence
• SecondTable: 0 occurrences

Expected result: Show 1 result of 4s in FirstTable in matrix visual 

• For Amount 3, 2, 1:

FirstTable and SecondTable match counts, so no rows should be shown for these amounts.

The same applies to the second table.

It seems like there is no solution for this 😞
thanks for any help

[v-ssriganesh]

Hi @mmiklauz1,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @mmiklauz1,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

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.

[v-ssriganesh]

Hi @mmiklauz1,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[techies]

Hi @mmiklauz1 please try this

create a table like this 

Amounts =

UNION (

    DISTINCT ( SELECTCOLUMNS ( Table1, "Amount", Table1[Amount] ) ),

    DISTINCT ( SELECTCOLUMNS ( Table2, "Amount", Table2[Amount] ) )

)

then the measures---

Table1 Count =

CALCULATE (

    COUNTROWS ( Table1 ),

    TREATAS ( VALUES ( Amounts[Amount] ), Table1[Amount] )

)

Extra in Table1 =

VAR Count1 = [Table1 Count]

VAR Count2 = [Table2 Count]

RETURN

IF (Count1 > Count2, Count1 - Count2)

Table2 Count =

CALCULATE (

    COUNTROWS ( Table2 ),

    TREATAS ( VALUES ( Amounts[Amount] ), Table2[Amount] )

)

Extra in Table2 =

VAR Count1 = [Table1 Count]

VAR Count2 = [Table2 Count]

RETURN

IF (Count2 > Count1, Count2 - Count1)

[mmiklauz1]

Hi @techies,  unfortunately it will not work because the final table should contain rows:
5

5

5

4

3 rows more of 5 in the first table compared to the second

and 1 row more of 4 in the first table compared to the second

[DataNinja777]

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]

Thank you DataNinja777, this almons solve my problem. But, for example the amount for 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

[mmiklauz21]

Thank you DataNinja777, this almons solve my problem. But, for example the amount for 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

[mmiklauz21]

Thank you DataNinja777, this almons solve my problem. But, for example the amount for 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

[mmiklauz21]

Thank you DataNinja777, this almons solve my problem. But, for example the amount for 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

[mmiklauz21]

Thank you DataNinja777, this almons solve my problem. But, for example the amount for 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

[mmiklauz21]

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

[mmiklauz21]

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