Forum Discussion

rayonlinenz's avatar
rayonlinenz
New Member
10 months ago
Solved

2 matrix tables each with slicers - 3rd table that does a calculation

Hi all    This is my first post and this site has been a regular location to trawl through for education.    I have 2 separate matrix tables and each of them have 5 or so different slicers (dropd...
Need Help
Show and Tell
  • v-echaithra's avatar
    v-echaithra
    10 months ago

    Hi rayonlinenz ,

    In DAX, you can’t aggregate an entire table directly.
    For example, this is invalid: Left Score = SUM(Append1)

    Functions such as SUM, COUNT, and similar must always reference a column, not a table. Because of this, the error you’re seeing is not related to Append1 being an appended table appended tables behave the same as any other table in DAX.

    Correct approach for the Left Score measure

    Left Score :=
    CALCULATE (
    COUNT ( Append1[Event_Number] ),
    ALL ( Append1_disconnect )
    )


    This ensures the measure respects the left-side slicers and matrix context while ignoring filters from the disconnected right-side table.

    Hope this helps.
    Thank you.

rayonlinenz's avatar
rayonlinenz
New Member
10 months ago

Thank you so much for this. I've been busy at work and have some time to go over this. 

 

Starting with the left side selection

The measure I am using is [Count_], the table name is Append1 

It is an appended table. 

The matrix table is set up with Hour Number and Day of the Week.

Within my main data which is appended. I've tried using DAX to create a new measure and typed in 

 

Left Score = SUM(Append1
 
I cannot enter the Append1 table. Is this because it's an appended table? 
 
Following that, I tried the following:
Left Score = COUNT(Append1[Event_Number]) ALL (Append1_disconnect)
 
However it says "the syntax for ALL is incorrect (DAX/Count(Append1[Event Number]) ALL (Append1_disconnect))).
 
COUNT(Append1[Event_Number]) 
This is the measue that I use as written out for the left table.
 
Append1_disconnect
This is my disconnected duplicated data table. It is duplicated from the "Append1" table. 
 
 
Thanks so much again. 🙂 
v-echaithra's avatar
v-echaithra
Icon for Community Support rankCommunity Support
10 months ago

Hi rayonlinenz ,

In DAX, you can’t aggregate an entire table directly.
For example, this is invalid: Left Score = SUM(Append1)

Functions such as SUM, COUNT, and similar must always reference a column, not a table. Because of this, the error you’re seeing is not related to Append1 being an appended table appended tables behave the same as any other table in DAX.

Correct approach for the Left Score measure

Left Score :=
CALCULATE (
COUNT ( Append1[Event_Number] ),
ALL ( Append1_disconnect )
)


This ensures the measure respects the left-side slicers and matrix context while ignoring filters from the disconnected right-side table.

Hope this helps.
Thank you.

  • rayonlinenz's avatar
    rayonlinenz
    New Member
    10 months ago

    Thank you for this. I log onto my work laptop at home and got it all working. Really appreciate this and will definitely write my notes up. 😀👍 Merry Christmas and a Happy New Year from NZ!