cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RRosen Frequent Visitor
Frequent Visitor

Problems with multiple filter

Dear Friends,
I am new to PBI and finding problems in getting the solution above,
I really don´t know if there is a problem with the logic or with the function itself, 
I have tried with if() function but Dax compiler keeps saying that I am not allowed to do such filter or if condition.
thanks and best regards !
 
testcalculate =
VAR ano =
FORMAT ( YEAR ( MAX ( FatoOpex[Data] ) ) - 3; "####" )
RETURN

CALCULATE(sum(FatoOpex[valor]);

FILTER( FatoOpex;
FatoOpex[exDiretoria] = "0" &&
FatoOpex[DimCalendario.Ano]=ano)
 
||

FILTER(FatoOpex;
FatoOpex[exDiretoria] = "1" &&
FatoOpex[DimNatureza.idNatureza]="7" &&
FatoOpex[DimCalendario.Ano]=ano))
1 ACCEPTED SOLUTION

Accepted Solutions
Iamnvt Member
Member

Re: Problems with multiple filter

hi,

 

the syntax:

CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )

treat Filter1, Filter2 with the AND operations.

 

to use the OR operations, you need to use it within the same filter. 

testcalculate =
VAR ano =
    FORMAT ( YEAR ( MAX ( FatoOpex[Data] ) ) - 3; "####" )
RETURN
    CALCULATE (
        SUM ( FatoOpex[valor] );
        FILTER(FatoOpex; 
OR ( FatoOpex[exDiretoria] = "0" && FatoOpex[DimCalendario.Ano] = ano; FatoOpex[exDiretoria] = "1" && FatoOpex[DimNatureza.idNatureza] = "7" && FatoOpex[DimCalendario.Ano] = ano ) )

 

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Problems with multiple filter

Give this a try

testcalculate =
VAR ano =
    FORMAT ( YEAR ( MAX ( FatoOpex[Data] ) ) - 3; "####" )
RETURN
    CALCULATE (
        SUM ( FatoOpex[valor] );
        OR (
            FatoOpex[exDiretoria] = "0"
                && FatoOpex[DimCalendario.Ano] = ano;
            FatoOpex[exDiretoria] = "1"
                && FatoOpex[DimNatureza.idNatureza] = "7"
                && FatoOpex[DimCalendario.Ano] = ano
        )
    )
RRosen Frequent Visitor
Frequent Visitor

Re: Problems with multiple filter

thank you for your try, but have this :

"The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

 

 

Iamnvt Member
Member

Re: Problems with multiple filter

hi,

 

the syntax:

CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )

treat Filter1, Filter2 with the AND operations.

 

to use the OR operations, you need to use it within the same filter. 

testcalculate =
VAR ano =
    FORMAT ( YEAR ( MAX ( FatoOpex[Data] ) ) - 3; "####" )
RETURN
    CALCULATE (
        SUM ( FatoOpex[valor] );
        FILTER(FatoOpex; 
OR ( FatoOpex[exDiretoria] = "0" && FatoOpex[DimCalendario.Ano] = ano; FatoOpex[exDiretoria] = "1" && FatoOpex[DimNatureza.idNatureza] = "7" && FatoOpex[DimCalendario.Ano] = ano ) )

 

View solution in original post

RRosen Frequent Visitor
Frequent Visitor

Re: Problems with multiple filter

thank you ! it worked fine!


Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,473 guests
Please welcome our newest community members: