topic Fix DAX measure of conditionals in DAX Commands and Tips

Fix DAX measure of conditionals

Rai_BI — Sat, 10 Feb 2024 18:27:19 GMT

Hello friends, can anyone help me adjust the DAX code below?

I need to make the "RCA2" value also return in the total grid when the first condition is satisfied. Or make the "RCA3" value also return in the total grid when the second condition is satisfied. Or make the "Both" value also return in the total grid when the third condition is satisfied.

It's in scope = VAR _RCA2inScope = ISINSCOPE('d_Clientes - 8040'[Internal Seller]) VAR _RCA3inScope = ISINSCOPE('d_Clientes - 8040'[Equipment Seller]) VAR_Swith = SWITCH( TRUE(), _RCA2inScope && NOT _RCA3inScope, "RCA2", _RCA3inScope && NOT _RCA2inScope, "RCA3", _RCA2inScope && _RCA3inScope, "Both" ) RETURN _Swith

Re: Fix DAX measure of conditionals

lbendlin — Sat, 10 Feb 2024 21:42:53 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Fix DAX measure of conditionals

Rai_BI — Sat, 10 Feb 2024 22:00:58 GMT

You can download here: example pbix file - swith grid total.pbix

Re: Fix DAX measure of conditionals

talespin — Sun, 11 Feb 2024 08:07:48 GMT

Hi Rai_BI

I can only thisnk of one way, using three separate measures as there is no scope for Totals.

It's in scope =

VAR _RCA2inScope = ISINSCOPE('customer table'[Name Seller1])

VAR _RCA2Filter = HASONEFILTER('customer table'[Name Seller1])

VAR _Val = IF( _RCA2inScope && _RCA2Filter , "RCA2", "RCA1")

RETURN _Val

Re: Fix DAX measure of conditionals

Rai_BI — Sun, 11 Feb 2024 19:46:20 GMT

Thank you, but it is no working as expected. The returned value in running total is incorrect.

Re: Fix DAX measure of conditionals

lbendlin — Sun, 11 Feb 2024 22:21:28 GMT

You seem to be missing a data model. Separate customers from Sellers, and add a mapping table. Then add a calendar table. Keep your goals table disconnected and use TREATAS to link it to the sellers and the calendar.

Re: Fix DAX measure of conditionals

talespin — Mon, 12 Feb 2024 00:25:34 GMT

hi Rai_BI

The solution which I shared, I created it using the pbix file which you provided, I believe you are trying to apply this solution to do something else, which may not work, You need to explain exactly what you are doing, with some sample data(Mock up data will do).

Re: Fix DAX measure of conditionals

Rai_BI — Tue, 13 Feb 2024 18:17:42 GMT

It is exactly what i said. I need to make the "RCA2" value also return in the total grid when the first condition is satisfied. Or make the "RCA3" value also return in the total grid when the second condition is satisfied. Or make the "Both" value also return in the total grid when the third condition is satisfied.

Re: Fix DAX measure of conditionals

talespin — Wed, 14 Feb 2024 04:14:34 GMT

hi Rai_BI

Sorry, I can't think of a possible solution, this is not a perfect solution, it is flawed. Its just like Elephants display teeth 😄

RCA1 in Scope =

VAR _RCA2inScope = ISINSCOPE('customer table'[Name Seller1])

VAR _RCA3inScope = ISINSCOPE('customer table'[Name Seller2])

VAR _RCA2Filter = HASONEFILTER('customer table'[Name Seller1])

VAR _Val = IF( NOT _RCA3inScope && _RCA2inScope && _RCA2Filter , "RCA2", "RCA1")

RETURN _Val

RCA2 in Scope =

VAR _RCA2inScope = ISINSCOPE('customer table'[Name Seller1])

VAR _RCA3inScope = ISINSCOPE('customer table'[Name Seller2])

VAR _RCA3Filter = HASONEFILTER('customer table'[Name Seller2])

VAR _Val = IF( NOT _RCA2inScope && _RCA3inScope && _RCA3Filter , "RCA3", "RCA2")

RETURN _Val

Both =

VAR _RCA2inScope = ISINSCOPE('customer table'[Name Seller1])

VAR _RCA3inScope = ISINSCOPE('customer table'[Name Seller2])

VAR _RCA2Filter = HASONEFILTER('customer table'[Name Seller2])

VAR _RCA3Filter = HASONEFILTER('customer table'[Name Seller2])

VAR _Val = IF( _RCA2inScope && _RCA3inScope && _RCA3Filter , "Both", "Both")

RETURN _Val