Filtering a Custom Column

[fergu513]

Here's my custom column:

Retained Control =

VAR ThisCostCenter = 'Transactions'[COST_CENTER_ID]

VAR ThisYM = VALUE('Transactions'[PROCESS_YM])  -- Convert text to number

-- Find first valid PROCESS_YM for this cost center

VAR FirstRow =

    CALCULATETABLE(

        TOPN(

            1,

            FILTER(

                ALL('Transactions'),

                'Transactions'[COST_CENTER_ID] = ThisCostCenter &&

                NOT ISBLANK('Transactions'[Ext_Sales]) &&

                NOT ISBLANK('Transactions'[ACQUISITION_YR]) &&

                'Transactions'[ACQUISITION_YR] <> 0

            ),

            VALUE('Transactions'[PROCESS_YM]), ASC

        )

    )

VAR FirstYM = MAXX(FirstRow, VALUE('Transactions'[PROCESS_YM]))

-- Add 11 months to FirstYM to get inclusive 12-month window

VAR _FirstDate = DATE(DIVIDE(FirstYM, 100), MOD(FirstYM, 100), 1)

VAR _LastDate = EDATE(_FirstDate, 11)

VAR LastYM = YEAR(_LastDate) * 100 + MONTH(_LastDate)

RETURN

    // IF (

    //     ISBLANK('Transactions'[ACQUISITION_YR]) || 'Transactions'[ACQUISITION_YR] = 0,

    //     2,  -- No acquisition year

        IF (

            NOT ISBLANK(FirstYM) &&

            ThisYM <= LastYM,

            0,  -- Retained

            1   -- Not retained

        )

    // )

Now I have a measure where I utlize this column:

Ext. Sales PY =

VAR SelectedRC = SELECTEDVALUE(Transactions[Retained Control])

VAR Base = CALCULATE(

    [03Total_Sales_PY],

    Transactions[Whse_Type] = "Branch"

)

RETURN

SWITCH(

    TRUE(),

        NOT HASONEVALUE(Transactions[Retained Control]),

        Base,  -- Handle grand total / no filter

    SelectedRC = 0,

        BLANK()

        ,

    SelectedRC = 1,

        CALCULATE(

            [03Total_Sales_PY],

            Transactions[Whse_Type] = "Branch",

            Transactions[Retained Control] = 0 ||

            Transactions[ACQUISITION_YR] = 0 ||

            ISBLANK(Transactions[ACQUISITION_YR])

        )

)

It seems that for some cost centers, specifically ones that ONLY have 0 in retained control, I get the variable 'Base' returned, when I want it to be blank. 
The first part of my switch statement is getting triggered for these cost centers, despite the fact that the value for Retained Control is 0.

Can someone help me make my return statement work for these cost centers?

It works as intended when I am viewing a cost center that has both 1 and 0 in the custom column (retained control)

[v-sdhruv]

Hi @fergu513 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @fergu513 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @fergu513 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @fergu513 ,

You can try:

Ext. Sales PY =
VAR MinRC = MIN(Transactions[Retained Control])
VAR MaxRC = MAX(Transactions[Retained Control])
VAR AllZero = MinRC = 0 && MaxRC = 0
VAR AllOne = MinRC = 1 && MaxRC = 1

RETURN
SWITCH(
TRUE(),
AllZero, BLANK(),
AllOne,
CALCULATE(
[03Total_Sales_PY],
Transactions[Whse_Type] = "Branch",
Transactions[Retained Control] = 0 ||
Transactions[ACQUISITION_YR] = 0 ||
ISBLANK(Transactions[ACQUISITION_YR])
),
BLANK() 
)

Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @fergu513 ,
Force SELECTEDVALUE to return 0 if all values are 0.
VAR SelectedRC =
IF(
HASONEVALUE(Transactions[Retained Control]),
VALUES(Transactions[Retained Control]),
IF(
MIN(Transactions[Retained Control]) = 0 && MAX(Transactions[Retained Control]) = 0,
0,
BLANK()
)
)

Then update your switch function.
Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

RETURN
SWITCH(
TRUE(),
SelectedRC = 0,
BLANK(),
SelectedRC = 1,
CALCULATE(
[03Total_Sales_PY],
Transactions[Whse_Type] = "Branch",
Transactions[Retained Control] = 0 ||
Transactions[ACQUISITION_YR] = 0 ||
ISBLANK(Transactions[ACQUISITION_YR])
),
TRUE(), // default case
BLANK()
)

[fergu513]

Hi, this didn't work. 

When I filter the measure for retained control = 0, it seems that some row instances get caught by the 0 part of the switch statment, and some get caught by the "not hasonevalue" part of the switch statement.
How do i make it so that every single row instances gets caught by the 0 part of the switch statement when I filter for retained control = 0?

[v-sdhruv]

Hi @fergu513 ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[fergu513]

Hi, it did not work. Still need help

[bhanu_gautam]

@fergu513 , Try using

DAX
Ext. Sales PY =
VAR SelectedRC = SELECTEDVALUE(Transactions[Retained Control])
VAR Base = CALCULATE(
[03Total_Sales_PY],
Transactions[Whse_Type] = "Branch"
)

RETURN
SWITCH(
TRUE(),
NOT HASONEVALUE(Transactions[Retained Control]),
Base, -- Handle grand total / no filter
SelectedRC = 0,
BLANK(),
SelectedRC = 1,
CALCULATE(
[03Total_Sales_PY],
Transactions[Whse_Type] = "Branch",
Transactions[Retained Control] = 0 ||
Transactions[ACQUISITION_YR] = 0 ||
ISBLANK(Transactions[ACQUISITION_YR])
),
BLANK() -- Default case to handle any unexpected scenarios
)

[fergu513]

Thanks for the reply.

What is different about this DAX from what I originally posted?