Fix DAX measure of conditionals

[Rai_BI]

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

[lbendlin]

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-...

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/1447...

[Rai_BI]

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

[lbendlin]

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.  

[talespin]

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

[Rai_BI]

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

[talespin]

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

[Rai_BI]

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.

[talespin · Accepted solution]

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