Forum Discussion

klehar's avatar
klehar
Icon for Helper V rankHelper V
1 year ago
Solved

quarter vs month switcher

Im trying to design a dashboard where based on my filter selection in column QM with values  "Quarter" or "Month", my calculations on the table would change

My calculation

VAR qm = VALUES('Quarter or Month'[QM])

RETURN
 CALCULATE (

        [IQ TAT <1 Day]
        ,
        DATEADD ( 'DIM Date'[DATE_KY], -1, qm)
    )


but this is the error i get

 

 

 

how can i solve this?

Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    klehar 

     

    Hi, i think the problem is VALUES returns a variable, not a fixed keyword. 

     

    You may need to update it to below for another try

    VAR qm = SELECTEDVALUE('Quarter or Month'[QM], "None")
RETURN
    SWITCH(
        qm,
        "Month", CALCULATE([IQ TAT <1 Day], DATEADD('DIM Date'[DATE_KY], -1, MONTH)),
        "Quarter", CALCULATE([IQ TAT <1 Day], DATEADD('DIM Date'[DATE_KY], -1, QUARTER)),
        BLANK() 
    )

     Hope this helps:) 

3 Replies

  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    1 year ago

    klehar 

     

    Hi, i think the problem is VALUES returns a variable, not a fixed keyword. 

     

    You may need to update it to below for another try

    VAR qm = SELECTEDVALUE('Quarter or Month'[QM], "None")
RETURN
    SWITCH(
        qm,
        "Month", CALCULATE([IQ TAT <1 Day], DATEADD('DIM Date'[DATE_KY], -1, MONTH)),
        "Quarter", CALCULATE([IQ TAT <1 Day], DATEADD('DIM Date'[DATE_KY], -1, QUARTER)),
        BLANK() 
    )

     Hope this helps:) 

  • Alex_Sawdo's avatar
    Alex_Sawdo
    Icon for Resolver II rankResolver II
    1 year ago

    You should be able to accomplish this using a field parameter, rather than a DAX measure/column. To do this, at the top of the screen go to this path: Modeling->New parameter->Fields. Then, select your Month and Quarter fields (assuming you have a date dimension in your model). You will then have a field parameter that lets you dynamically choose between either the Month or Quarter column in your date dimension. 

    Here's the documentation for Field Parameters as well, if you want to look further into them: Use report readers to change visuals (preview) - Power BI | Microsoft Learn

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    You could use a calculation group, with separate items for month and quarter, e.g.

    Month =
CALCULATE ( SELECTEDMEASURE(), DATEADD ( 'DIM Date'[DATE_KY], -1, MONTH ) )

Quarter =
CALCULATE ( SELECTEDMEASURE(), DATEADD ( 'DIM Date'[DATE_KY], -1, QUARTER ) )

    Then put the calculation group into the slicer instead of the 'Quarter or Month' table you have currently.