Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

If condition DAX

Hi Team,

Please help me writing the below in DAX as it is written in Tableau.

 

 

if
[APO Location]="(All)" AND [APO Region]!="(All)"
THEN
IFNULL(SUM([Supply Region]),0)
+IFNULL(SUM([Act Production]),0)
+IFNULL(SUM([Act Supply Otherbottler]),0)

ELSE
if
[APO Location]!="(All)" AND [APO Region]="(All)"
THEN
IFNULL(sum([Total Receipts]),0)
+IFNULL(SUM([Act Production]),0)
+IFNULL(SUM([Act Supply Otherbottler]),0)

ELSE
if
[APO Location]!="(All)" AND [APO Region]!="(All)"
THEN
IFNULL(sum([Total Receipts]),0)
+IFNULL(SUM([Act Production]),0)
+IFNULL(SUM([Act Supply Otherbottler]),0)

ELSE
IFNULL(sum([Production]),0)+IFNULL(sum([Supply Otherbottler]),0)
+IFNULL(sum([Substitution Receipt]),0)
+IFNULL(SUM([Act Production]),0)
+IFNULL(SUM([Act Supply Otherbottler]),0)
END
END
END

 

 

Thanks !

Need Help

7 Replies

  • AjithPrasath's avatar
    AjithPrasath
    Resolver II
    3 years ago

    Hi Anonymous ,

     

     Please try the below code:

    Result =
IF (
    [APO Location] = "(All)" && [APO Region] <> "(All)",
    IFNULL ( SUM ( [Supply Region] ), 0 ) + IFNULL ( SUM ( [Act Production] ), 0 ) + IFNULL ( SUM ( [Act Supply Otherbottler] ), 0 ),
    IF (
        [APO Location] <> "(All)" && [APO Region] = "(All)",
        IFNULL ( SUM ( [Total Receipts] ), 0 ) + IFNULL ( SUM ( [Act Production] ), 0 ) + IFNULL ( SUM ( [Act Supply Otherbottler] ), 0 ),
        IF (
            [APO Location] <> "(All)" && [APO Region] <> "(All)",
            IFNULL ( SUM ( [Total Receipts] ), 0 ) + IFNULL ( SUM ( [Act Production] ), 0 ) + IFNULL ( SUM ( [Act Supply Otherbottler] ), 0 ),
            IFNULL ( SUM ( [Production] ), 0 ) + IFNULL ( SUM ( [Supply Otherbottler] ), 0 ) + IFNULL ( SUM ( [Substitution Receipt] ), 0 ) + IFNULL ( SUM ( [Act Production] ), 0 ) + IFNULL ( SUM ( [Act Supply Otherbottler] ), 0 )
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      AjithPrasath ,

      Is this new colum or Custom column in PowerQuery, if its a new column then Isnull function is not there ryt ?

       

  • AjithPrasath's avatar
    AjithPrasath
    Resolver II
    3 years ago

    Hi Anonymous ,

      You can use it as dax. Please let me know if you have any issue in that.

     

    Best Regards,

    Ajith Prasath

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    AjithPrasath ,

    I am not able use in Measure below is the error.

     

    In column ifnull function is not supporting.

     

    Please help writting same in PowerQuery custom column.

     

     

     

     

    • AjithPrasath's avatar
      AjithPrasath
      Resolver II
      3 years ago

      Can you try the below code:

       

      Result =
IF (
    [APO Location] = "(All)" && [APO Region] <> "(All)",
    COALESCE ( SUM ( [Supply Region] ), 0 ) + COALESCE ( SUM ( [Act Production] ), 0 ) + COALESCE ( SUM ( [Act Supply Otherbottler] ), 0 ),
    IF (
        [APO Location] <> "(All)" && [APO Region] = "(All)",
        COALESCE ( SUM ( [Total Receipts] ), 0 ) + COALESCE ( SUM ( [Act Production] ), 0 ) + COALESCE ( SUM ( [Act Supply Otherbottler] ), 0 ),
        IF (
            [APO Location] <> "(All)" && [APO Region] <> "(All)",
            COALESCE ( SUM ( [Total Receipts] ), 0 ) + COALESCE ( SUM ( [Act Production] ), 0 ) + COALESCE ( SUM ( [Act Supply Otherbottler] ), 0 ),
            COALESCE ( SUM ( [Production] ), 0 ) + COALESCE ( SUM ( [Supply Otherbottler] ), 0 ) + COALESCE ( SUM ( [Substitution Receipt] ), 0 ) + COALESCE ( SUM ( [Act Production] ), 0 ) + COALESCE ( SUM ( [Act Supply Otherbottler] ), 0 )
        )
    )
)

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    AjithPrasath ,

    This works fine but can you help me in writing the same in Power Query custom column.

     

    Thanks !

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      HI Anonymous,

      In fact, power query does not suitable for this type of operation.

      They should be more suitable to do data transform instead of calculation and aggregation with different filters. (you may need to extract the previous query step result as parameter and nested with multiple M query functions when you tried to do a simple aggregate calculations)

      Regards,

      Xiaoxin Sheng