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 @adjohnson2 ,
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.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
125 | |
75 | |
65 | |
56 | |
55 |
User | Count |
---|---|
199 | |
104 | |
88 | |
79 | |
77 |