topic Re: Filtering a Custom Column in DAX Commands and Tips

Filtering a Custom Column

Anonymous — Mon, 19 May 2025 15:13:38 GMT

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(

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)

Re: Filtering a Custom Column

bhanu_gautam — Tue, 20 May 2025 06:53:32 GMT

Anonymous , 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
)

Re: Filtering a Custom Column

Anonymous — Tue, 20 May 2025 13:18:50 GMT

Thanks for the reply.

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

Re: Filtering a Custom Column

v-sdhruv — Wed, 21 May 2025 10:39:17 GMT

Hi Anonymous ,
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

Re: Filtering a Custom Column

Anonymous — Wed, 21 May 2025 15:49:14 GMT

Hi, it did not work. Still need help

Re: Filtering a Custom Column

v-sdhruv — Thu, 22 May 2025 11:58:37 GMT

Hi Anonymous ,
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()
)

Re: Filtering a Custom Column

Anonymous — Thu, 22 May 2025 13:38:28 GMT

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?

Re: Filtering a Custom Column

v-sdhruv — Fri, 23 May 2025 11:51:53 GMT

Hi Anonymous ,

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

Re: Filtering a Custom Column

v-sdhruv — Mon, 26 May 2025 12:24:29 GMT

Hi Anonymous ,
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

Re: Filtering a Custom Column

v-sdhruv — Thu, 29 May 2025 05:41:51 GMT

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

Re: Filtering a Custom Column

v-sdhruv — Mon, 02 Jun 2025 06:06:43 GMT

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