Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Return with If statement in dax

I am trying to get the AMT3 if the measure is divisible by 13 get Amt1, otherwise i would like to get the current week value Amt2. but the code below produce a blank outcome. is there a way to fix the code below ? many thanks in advance.

Amt3 =

VAR Amt1 =
CALCULATE(
    [measure],
    FILTER('Calendar', MOD( 'Calendar'[Fiscal Week num],13 ) = 0 ))

VAR Amt2=
CALCULATE(
    [measure],
    FILTER('Calendar', 'Calendar'[get_current_week] = TRUE() ) )


Return
IF( MOD(MAX( 'Calendar'[Fiscal Week num]),13 ) = 0 , Amt1,  Amt2 )

where get_current_week is a calculated column
get_current_week = WEEKNUM([Date],2) = WEEKNUM(TODAY(),2)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Please update the formula of measure as below and check if it can return the expected result.

    Amt3 =
VAR Amt1 =
    CALCULATE (
        [measure],
        FILTER ( 'Calendar', MOD ( 'Calendar'[Fiscal Week num], 13 ) = 0 ),
        ALL ( 'yourfacttable' )
    )
VAR Amt2 =
    CALCULATE (
        [measure],
        FILTER ( 'Calendar', 'Calendar'[get_current_week] = TRUE () ),
        ALL ( 'yourfacttable' )
    )
RETURN
    IF ( MOD ( MAX ( 'Calendar'[Fiscal Week num] ), 13 ) = 0, Amt1, Amt2 )

     

    If the above one can't help you figure out, please provide some raw data in your table (exclude sensitive data) with Text format, the formula of [measure] and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please update the formula of measure as below and check if it can return the expected result.

    Amt3 =
VAR Amt1 =
    CALCULATE (
        [measure],
        FILTER ( 'Calendar', MOD ( 'Calendar'[Fiscal Week num], 13 ) = 0 ),
        ALL ( 'yourfacttable' )
    )
VAR Amt2 =
    CALCULATE (
        [measure],
        FILTER ( 'Calendar', 'Calendar'[get_current_week] = TRUE () ),
        ALL ( 'yourfacttable' )
    )
RETURN
    IF ( MOD ( MAX ( 'Calendar'[Fiscal Week num] ), 13 ) = 0, Amt1, Amt2 )

     

    If the above one can't help you figure out, please provide some raw data in your table (exclude sensitive data) with Text format, the formula of [measure] and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards