Forum Discussion

naninamu's avatar
naninamu
Icon for Helper IV rankHelper IV
1 year ago
Solved

Merging tables with large numbers of rows

Hi everyone. In theory I know how to do this, but the volume of data is giving me issues so need some help.   Essentially, and this is simplified for the sake of this example, I have 2 tables.    ...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    There's a typo in your expected result - g is appearing 10 times not 9 times.  So the total is 911, not 902.

     

    You may want to try TREATAS as an alternative, it will likely perform better with larger data sets

     

naninamu's avatar
naninamu
Icon for Helper IV rankHelper IV
1 year ago

I've been trying to understand how exactly "Item Count" and "Value Sum" work. Could you maybe in english just explain the logic behind them? I'm new to DAX so can't quite work it out at present! Thanks in advance.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

First you need to consider the data model. I chose this

but that is my personal choice. Depending on your business scenario you might need a different data model, for example without linking the tables.

 

To better understand the DAX you can use DAXFormatter.com

 

Item count =
VAR a =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'Group IDs'[Group], 'Group Item Values'[Item] ),
        "v", CALCULATE ( SUM ( 'Group Item Values'[Value] ) ),
        "ct", CALCULATE ( COUNTROWS ( Groups ) )
    )
RETURN
    SUMX ( FILTER ( a, NOT ISBLANK ( [v] ) ), [ct] )

First we materialize all combinations of Group and Item.

Then we compute (separately) the item values and the group count

The item values are required to get the right granularity. But due to the cross join in SUMMARIZECOLUMNS we need to then exclude the blanks.  For the rest we add up the counts.

 

Value Sum works exactly the same, but we don't need to worry about the filter as here blanks won't contribute to the sum. You could add it if you wanted.

 

Value Sum =
VAR a =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'Group IDs'[Group], 'Group Item Values'[Item] ),
        "v", CALCULATE ( SUM ( 'Group Item Values'[Value] ) ),
        "ct", CALCULATE ( COUNTROWS ( Groups ) )
    )
RETURN
    SUMX ( a, [v] * [ct] )