Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Lista de matrices

Estoy tratando de crear una matriz en DAX y tener mis cálculos usando esa lista para filtrar. ¿Es esto posible en 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")

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    En lugar de ese enfoque, agregaría una columna para tener el cuarto como un entero (1-4), almacenarlo en una variable llamada thisquarter en su medida, y usar FILTER(ALL(Table[Quarter]), Table[Quarter] <- thisquarter) en su enfoque CALCULATE en lugar del enfoque "in Quarters" (también agregue un término para filtrarlo al año actual).

    saludos

    palmadita

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I literally don't understand what you said.

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hola @adjohnson2 ,

    Modifiqué tu fórmula a continuación.

    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"
                }
        )
    )

    Y si desea hacerlo con M en el editor de consultas, podría hacer referencia a este subproceso similar:

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