Array list

[Anonymous]

I'm trying to create a array in DAX and have my calculations using that list for filtering. Is this possible in DAX.

Total Backlog = 
var Quarters =
SWITCH(TRUE(),
MONTH(TODAY()) in {1,2,3}, {"2020 Q1"},
MONTH(TODAY()) in {4,5,6}, {"2020 Q1", "2020 Q2"},
MONTH(TODAY()) in {7,8,9}, {"2020 Q1", "2020 Q2", "2020 Q3"},
MONTH(TODAY()) in {10,11,12}, {"2020 Q1", "2020 Q2", "2020 Q3", "2020 Q4"}
)

return CALCULATE(sum('Arc_Inventory_Export (Raw)'[BestEstCost])
, 'Arc_Inventory_Export (Raw)'[Status] in {"Construction", "Design", "Follow-up Needed", "Inspection Needed", "Project Identified", "Ready to Schedule", "Scheduled", "Inspection Complete"}
, FILTER('PSK Table', 'PSK Table'[PSK] in {"PSK403", "PSK404", "PSK405"})
, 'Arc_Inventory_Export (Raw)'[CompletionQuarter] in Quarters
, 'Arc_Inventory_Export (Raw)'[Blank Complete Date & Blank Quarter] = "0")

[v-xuding-msft]

Hi @Anonymous ，

I modified your formula to below.

Total Backlog =
VAR MonthToday =
    MONTH ( TODAY () )
VAR Quarters =
    SWITCH (
        TRUE (),
        MonthToday IN { 1, 2, 3 }, "2020 Q1",
        MonthToday IN { 4, 5, 6 }, "2020 Q1, 2020 Q2",
        MonthToday IN { 7, 8, 9 }, "2020 Q1, 2020 Q2, 2020 Q3",
        MonthToday IN { 10, 11, 12 }, "2020 Q1, 2020 Q2, 2020 Q3, 2020 Q4"
    )
RETURN
    CALCULATE (
        SUM ( 'Arc_Inventory_Export (Raw)'[BestEstCost] ),
        FILTER (
            'PSK Table',
            'PSK Table'[PSK] IN { "PSK403", "PSK404", "PSK405" }
                && SEARCH ( 'Arc_Inventory_Export (Raw)'[CompletionQuarter], Quarters,, 999 ) <> 999
                && MAX ( 'Arc_Inventory_Export (Raw)'[Blank Complete Date & Blank Quarter] ) = "0"
                && MAX ( 'Arc_Inventory_Export (Raw)'[Status] )
                    IN {
                    "Construction",
                    "Design",
                    "Follow-up Needed",
                    "Inspection Needed",
                    "Project Identified",
                    "Ready to Schedule",
                    "Scheduled",
                    "Inspection Complete"
                }
        )
    )

And if you want to do it with M in query editor, you could reference this similar thread:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Create-Array-List-with-DAX/td-p/961668

[mahoneypat]

Instead of that approach, I would add a column to have the quarter as an integer (1-4), store it in a variable called thisquarter in your measure, and use FILTER(ALL(Table[Quarter]), Table[Quarter] <= thisquarter) in your CALCULATE instead of the "in Quarters" approach (also add a term to filter it to the current year).

Regards,

Pat

[Anonymous]

I literally don't understand what you said.