DAX calculate with multiple criteria

[Evelin_]

Hello, I would like to have a measure where I want to calculate a share for actual and for Target, and then I want to highlight the difference between them. For the share I have to get from the database 2 VAR, 1 with 1 filter but the other one with 2 filter like this:

VAR 1BP=

Calculate(

    SUM(Database[Volume in UC]),

    Filter(

        Database,

         Database[Version] = "BP"

        && Database[xxx] = "SS"

    )

VAR 2BP=

Calculate(

    SUM(Database[Volume in UC]),

         Database[Version] = "BP"

    )

VAR 3 SHARE=

divide (VAR1BP , VAR2BP , -1) ---> here I should get something like 68% (it should be under 100%)

VAR 1ACT=

Calculate(

    SUM(Database[Volume in UC]),

    Filter(

        Database,

         Database[Version] = "ACT"

        && Database[xxx] = "SS"

    )

VAR 2ACT=

Calculate(

    SUM(Database[Volume in UC]),

         Database[Version] = "ACT"

    )

VAR 4 SHARE=

divide (VAR1ACT , VAR2ACT , -1) ---> here I should get something like 68% (it should be under 100%)

 VAR 5 = VAR 3 SHARE - VAR 4 SHARE 

How Can I write this dax properly? I think I wont have problem with the RETURN part, I have problem with the VAR parts.

Thank you in advance!

[AntrikshSharma · Accepted solution]

@Evelin_ From the DAX code I can spot couple of mistakes. You are using Database table reference inside CALCULATE which is respecting the existing Filter Context so before it can be filtered for BP and SS it already gets filtered by whatever is the active Filter Context and the table returned by FILTER will be empty, try this instead:

CALCULATE (
    SUM ( Database[Volume in UC] ),
    Database[Version] = "BP"
        && Database[xxx] = "SS"
)

The above will ensure it ignores the existing Filter context on these 2 columns.

And if you don't want to overwrite the existing filter context on Version and XXX then use this:

CALCULATE (
    SUM ( Database[Volume in UC] ),
    KEEPFILTERS ( Database[Version] = "BP" && Database[xxx] = "SS" )
)

[FreemanZ · Accepted solution]

hi @Evelin_ ,

try like:

Measure =

VAR 1BP=

Calculate(

    SUM(Database[Volume in UC]),

    Filter(

        Database,

         Database[Version] = "BP"

        && Database[xxx] = "SS"

    )

VAR 2BP=

Calculate(

    SUM(Database[Volume in UC]),

         Database[Version] = "BP"

    )

VAR 3SHARE= divide (1BP , 2BP ) - 1

RETURN 3SHARE

[Dangar332 · Accepted solution]

@Evelin_ 
You need to use Dax Formatter 

VAR BP_1 =
    CALCULATE (
        SUM ( Database[Volume in UC] ),
        FILTER ( Database, Database[Version] = "BP" && Database[xxx] = "SS" )
    )
VAR BP_2 =
    CALCULATE ( SUM ( Database[Volume in UC] ), Database[Version] = "BP" )
VAR SHARE_3 =
    DIVIDE ( BP_1, BP_2, -1 )
VAR ACT_1 =
    CALCULATE (
        SUM ( Database[Volume in UC] ),
        FILTER ( Database, Database[Version] = "ACT" && Database[xxx] = "SS" )
    )
VAR ACT_2 =
    CALCULATE ( SUM ( Database[Volume in UC] ), Database[Version] = "ACT" )
VAR SHARE_4 =
    DIVIDE ( ACT_1, ACT_2, -1 )
RETURN
    SHARE_3 - SHARE_4

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ · Accepted solution]

hi @Evelin_ ,

try like:

Measure =

VAR 1BP=

Calculate(

    SUM(Database[Volume in UC]),

    Filter(

        Database,

         Database[Version] = "BP"

        && Database[xxx] = "SS"

    )

VAR 2BP=

Calculate(

    SUM(Database[Volume in UC]),

         Database[Version] = "BP"

    )

VAR 3SHARE= divide (1BP , 2BP ) - 1

RETURN 3SHARE

[AntrikshSharma]

"How Can I write this dax properly?" What kind of help do you need here? Optimization or the results are incorrect?

[Evelin_]

Hello! 

the result is incorrect and I tried to fix it but I think I miss something, some digns or anything that can cause error 😞

Thank you for your answer!

Evelin

[AntrikshSharma · Accepted solution]

@Evelin_ From the DAX code I can spot couple of mistakes. You are using Database table reference inside CALCULATE which is respecting the existing Filter Context so before it can be filtered for BP and SS it already gets filtered by whatever is the active Filter Context and the table returned by FILTER will be empty, try this instead:

CALCULATE (
    SUM ( Database[Volume in UC] ),
    Database[Version] = "BP"
        && Database[xxx] = "SS"
)

The above will ensure it ignores the existing Filter context on these 2 columns.

And if you don't want to overwrite the existing filter context on Version and XXX then use this:

CALCULATE (
    SUM ( Database[Volume in UC] ),
    KEEPFILTERS ( Database[Version] = "BP" && Database[xxx] = "SS" )
)

[Evelin_]

Many many thanks, it worked!!