DAX how to rewrite this FILTER to CALCULATETABLE?

Question

DAX how to rewrite this FILTER to CALCULATETABLE?

ktflash

Default port

Points: 1400
More actions
January 21, 2021 at 2:10 pm

#3835880
Ahoi,

i have been starting to look into DAX and there is something i could not find an answer to somehow.
I am very new to DAX so im trying to understand the basics and have come across something bothers me.
How do i rewrite the following Query using CALCULATETABLE instead of FILTER?
```
EVALUATE 
 (
        (
            SUMMARIZECOLUMNS (
                'Table'[ID],
                'Table'[BEZ],
                FILTER (
                    'Table',
'Table'[ID] = "Act"
                        || 'Table'[BEZ] = "Plan"
                )
            )
    )
)
```
It works with Filter accessing different columns in an or condition, but when i replace it with CALCULATETABLE i get an error because a true false check can only be done for one column. Using || OR with the same column works, different columns [ID][BEZ] does not.
How do i make this work, accessing different columns and OR?
The following works to filter based 2 different columns + CALCULATETABLE but its an AND condition and not an OR compared to the first query.
```
EVALUATE
CALCULATETABLE(
CALCULATETABLE(
'Table',
'Table'[ID] = "Act"
),
'Table'[BEZ] = "Ist"
)
```
I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19127 More actions · Answer 1

How about this?

CALCULATETABLE
(
    'Table'
,   FILTER
    (
        'Table'
    ,   OR('Table'[ID] = "Act", 'Table'[BEZ] = "Ist") 
    )
)

ktflash Default port Points: 1400 More actions · Answer 2

Martin Schoombee wrote:

How about this?

CALCULATETABLE
(
    'Table'
,   FILTER
    (
        'Table'
    ,   OR('Table'[ID] = "Act", 'Table'[BEZ] = "Ist") 
    )
)

thanks

Does that mean i can not achieve this by only using CALCULATETABLE?

Since that is basically just using the filter and adding it on top of the CALCULATETABLE instead of replacing it

The thing that originally bothered me so much was this:

in the query is posted, you cant replace filter with CALCULATETABLE, unless you change to select only ID or only BEZ

This reply was modified 3 years, 10 months ago by ktflash.
This reply was modified 3 years, 10 months ago by ktflash.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Martin Schoombee SSCoach Points: 19127 More actions · Answer 3

Yes.

My understanding is that CALCULATETABLE(), because of the syntax and the fact that it allows you to provide multiple filters that will be applied in an "AND" fashion, will not allow you to use an "OR" (or equivalent) filter predicate. The workaround is use FILTER() as it will allow you to do that.