Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Filtering a Custom Column

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 PR...
  • v-sdhruv's avatar
    v-sdhruv
    1 year ago

    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

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply.

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