Want to filter based on a table, incorrect totals

DAX DAX Calculations
1

I know this issue is addressed elsewhere and I watched the video, but I still don’t get it :frowning:

Attached is my super simple PBIX file
Incorrect Totals Example (filter based on a table).pbix (18.5 KB)

What I want is to only see the Filtering Letters (A, B, C), their individual amounts and the total will show the total of only those Filtering Letters (1+1+1=3).

It looks like the answer involves SUMX but then there are several different approaches that I didn’t understand.

thanks,
Bill

2

@BillK ,

Please give this a go:

Count in Full Table = 

VAR CountSelVal = 
CALCULATE(
    COUNTROWS( 'Full Table' ),
    FILTER(
        ALL( 'Full Table' ),
        'Full Table'[Letter] = MAX( 'Filtering Table'[Letter] )
    )
)

VAR vTable = 
ADDCOLUMNS(
    VALUES( 'Filtering Table'[Letter] ),
    "@Count", CountSelVal
)

VAR Result =
IF( 
    HASONEVALUE( 'Filtering Table'[Letter] ),
    CountSelVal,
    SUMX(
        vTable,
        [@Count]
    )
)

RETURN
Result

image

The first issue is that your filtering table should NOT be connected to your fact table. This will lead to “referential integrity” problems, i.e., there are values in your fact table that are not in your “dimension” table (not really a dim table, but that’s the way you’ve got it configured in the data model). That’s the reason you’re getting the unexplained blank in your results.

This measure basically iterates through filtering table, counting the number of occurrences of each letter in the fact table. The vTable variable holds a virtual table with the results of this iteration.

In the Result variable, it looks whether there is a single specified value for Letter in the row of your table visual. If so, it returns the appropriate count measure. If not (totals row), it sums the count field (@Count) in the virtual table.

I hope this is helpful. Full solution file attached.

1 Like
3

Brian,
Huge help.
Pointing out the referential integrity issue is something I didn’t know and now will be careful to avoid in the future.
Thanks for explaining the process.
Very grateful,
Bill

1 Like
4

@BillK ,

Very glad this was helpful to you. This is one of those problems that looks simple at first glance, but has a lot more going on under the surface. I’ve actually put this one on my potential video topics list, since I think there are a number of points of general interest bundled up in your question.

– Brian

5

Brian,
You are right on all points.
The problem is fairly common. I noticed as my models have gotten more complex, I keep running into this problem.

Also it seems like the solution should be simple. I’m pretty sure that I understand the problem … that total isn’t just adding up the rows in the visual like an excel sum(). Instead it is removing the row filter context. But the code to solve the problem and the idea underlying it is complicated. Also, it seems like there are different ways to solve it and I don’t understand how they work or the trade offs. In Sam’s video, he was using Summarize.

Your solution worked for my simple example, but when I tried to apply it to my actual situation I again ran into problems. I using a cumulative total measure for a trial balance. I think I did something wrong when trying to adapt your solution to my specific problem. Hopefully, with more time studying and experimenting, I’ll figure it out. If I can’t figure it out, I know where to come for help. But a more comprehensive video on this would help. Maybe use DAX Studio to make the virtual table visible. I’m hoping DAX Studio will help me move to the next level of understanding.
Thanks,
Bill

6

@BillK ,

Yes, @sam.mckay and I use different approaches to resolving the incorrect totals problem. His requires less code, and generally runs faster. I think my approach is a bit more intuitive and easier to debug. My general strategy is “make it work, then make it fast”, so I typically use my approach first and then switch over if I need it to run faster.

In terms of understanding what’s going on with your DAX, particularly if it involves virtual tables like this problem does, IMO by far the best tool out there is Tabular Editor 3. It’s designed specifically for writing and debugging these types of complex measures. Here’s a recent video I did on using it to debug virtual tables, as well as a general overview video I did a while back. Later this month, we’ll be releasing a full length course on TE3 by our resident DAX wizard @AntrikshSharma.

If you need any help with the more complex case you mentioned, just give a shout…

  • Brian