Forum Discussion
2 matrix tables each with slicers - 3rd table that does a calculation
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.
Super UserHi rayonlinenz ,
Calculations in Power BI are context dependent meaning that in order to have the calculations properly setup you need to get the data from your different reports aspects. What I mean from this is that if you have two matrix with different setup (filters) the calculations on the third visual will not be able to get the values from the other two without having the information from the slicers you have for the other two.
This means that you need to have a more complex (depending on the number of filters) calculation on your
3rd visual that does the calculation based on all the filters from the other two visuals.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Advocate IIIIdea: use disconnected slicers as filter providers
Since your right-hand table is already based on a disconnected copy of the data table, you can treat both sides as “slicer providers” and push their selections into measures using TREATAS (or SELECTEDVALUE when single‑select is enough). This is a common pattern for comparing two independently sliced views of the same data.1. Capture left‑side selection
Assume:- Fact is your main data table (used by the left matrix).
- Fact_Right is the duplicated/disconnected table (used by the right matrix).
- Slicers on the left use dimensions like DimLocation, DimType, etc.
2. Capture right‑side selection (disconnected table)
For the right side, use TREATAS to map slicer selections on Fact_Right back to Fact:text
Right Score := VAR SelLocation = VALUES ( Fact_Right[Location] ) VAR SelType = VALUES ( Fact_Right[Type] ) RETURN CALCULATE ( [Base Measure], TREATAS ( SelLocation, Fact[Location] ), TREATAS ( SelType, Fact[Type] ) )
3. Proportion / comparison measure for the 3rd table
Now your 3rd table (or matrix) just shows a measure that compares the two scores:text
Proportion Score := DIVIDE ( [Left Score], [Right Score] )
4. Practical notes
- This approach scales: for each extra slicer dimension on the right table, add another TREATAS line in Right Score.
- If your slicers are guaranteed to be single‑select, you can replace VALUES with SELECTEDVALUE and simplify the logic.powerbi
- For very complex setups (many slicers, many dimensions), consider centralizing all slicer logic in dedicated “filter tables” and using a small set of comparison measures rather than duplicating the entire fact table, to keep the model performant.
- https://www.wiseowl.co.uk/power-bi/videos/dax-powerbi/disconnected-slicers/
- https://www.fourmoo.com/2019/02/19/do-you-know-how-to-use-multiple-disconnected-slicers-in-power-bi/
- https://www.edureka.co/community/284149/write-expressions-that-well-with-disconnected-tables-slicers
- https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Slice-value-based-on-disconnected-table-with-dynamic-values/m-p/2740064
- https://techcommunity.microsoft.com/blog/fasttrackforazureblog/comparative-analysis-by-different-values-in-same-dimension-in-power-bi/3283444
- https://www.youtube.com/watch?v=-T1hK5W4m8I
- https://www.youtube.com/watch?v=O0VsTLDTjss
If this helped, feel free to give it a kudo and mark the reply as the solution so future
Power BI explorers can find it more easily.
Disclaimer: This message was drafted with the assistance of Microsoft Copilot to ensure clarity and professionalism.
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(Append1I 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_disconnectThis is my disconnected duplicated data table. It is duplicated from the "Append1" table.Thanks so much again. 🙂
v-echaithraCommunity Support
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.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!
