The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
109 | |
68 | |
46 | |
42 |
User | Count |
---|---|
188 | |
86 | |
77 | |
74 | |
60 |