Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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")
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I literally don't understand what you said.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
63 | |
53 | |
39 | |
25 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |