topic Re: DAX calculate with multiple criteria in DAX Commands and Tips

DAX calculate with multiple criteria

Evelin_ — Wed, 13 Nov 2024 09:21:55 GMT

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!

Re: DAX calculate with multiple criteria

AntrikshSharma — Wed, 13 Nov 2024 09:53:54 GMT

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

Re: DAX calculate with multiple criteria

Evelin_ — Wed, 13 Nov 2024 09:58:41 GMT

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

Re: DAX calculate with multiple criteria

AntrikshSharma — Wed, 13 Nov 2024 10:05:37 GMT

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" ) )

Re: DAX calculate with multiple criteria

FreemanZ — Wed, 13 Nov 2024 10:59:36 GMT

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

Re: DAX calculate with multiple criteria

Dangar332 — Wed, 13 Nov 2024 11:19:14 GMT

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.

Re: DAX calculate with multiple criteria

Evelin_ — Wed, 13 Nov 2024 12:17:55 GMT

Many many thanks, it worked!!