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

10 Replies

  • 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?

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, it did not work. Still need help

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
    v-sdhruv
    Community Support
    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

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    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

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    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's avatar
    v-sdhruv
    Community Support
    1 year ago

    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