Forum Discussion

CornelisV's avatar
CornelisV
Icon for Helper IV rankHelper IV
1 year ago
Solved

Combine CALCULATION DAX in Measure

Dear all,

 

I have defined two different measure:

 

1.

Sales < 6 :=
   CALCULATE([Sales Amount] * 0.2,
             'Date'[Fiscal Month Number] < 6
             )
 
2.
Sales > 6 :=
   CALCULATE([Sales Amount] * 0.1,
             'Date'[Fiscal Month Number] > 6
             )
 
The measures works fine, but can yoiu explain me how to combine measure 1 and 2 in one, so that [Sales Amount] will be mulitiplied with 0.1 and 0.2 depending on month number? I suppose that IF condition should be impolemented.
 
Appreciate your thoughts and support,
 
Best regards,
 
Cornelis
Need Help
  • Idrissshatila's avatar
    Idrissshatila
    1 year ago

    Hello CornelisV ,

     

    try using the below dax.

     

    Sales =
CALCULATE (
    [Sales Amount] *
        IF (
            MAX ( 'Date'[Fiscal Month Number] ) < 6,
            0.2,   -- multiplier if month < 6
            0.1    -- multiplier if month >= 6
        )
)
  • CornelisV's avatar
    CornelisV
    1 year ago

    Dear Idrissshatila ,

     

    Thank you for your solution, this is very clear. It works.

     

    Good to see that IF statement can be implemented in the DAX, this is something that I cannot find it so easily back in DAX training book.

     

    Best regards,

     

    Cornelis.

7 Replies

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

    Hello CornelisV ,

     

    try using the below dax.

     

    Sales =
CALCULATE (
    [Sales Amount] *
        IF (
            MAX ( 'Date'[Fiscal Month Number] ) < 6,
            0.2,   -- multiplier if month < 6
            0.1    -- multiplier if month >= 6
        )
)
    • CornelisV's avatar
      CornelisV
      Icon for Helper IV rankHelper IV
      1 year ago

      Dear Idrissshatila ,

       

      Thank you for your solution, this is very clear. It works.

       

      Good to see that IF statement can be implemented in the DAX, this is something that I cannot find it so easily back in DAX training book.

       

      Best regards,

       

      Cornelis.

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

    Hi,

    In the Table visual that you are building, what are you dragging to the Row labels?  Share the download link of the PBI file and show the expected result very clearly.

    • CornelisV's avatar
      CornelisV
      Icon for Helper IV rankHelper IV
      1 year ago

      Dear Ashish_Mathur ,

       

      Idrissshatila  demonstrates the solution, you can see a screenshot of the table view with the solution. 

      I agree with you that I'd better to describe the problem in more details. 

       

      Best regards,

       

      Cornelis.

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

    Hi CornelisV ,

    You can achieve this through the use of the IF() or SWITCH() functions. Here is an example using SWITCH():

    Sales =
CALCULATE(
    [Sales Amount] *
        SWITCH(
            TRUE(),
            MAX('Date'[Fiscal Month Number]) < 6, 0.2,
            MAX('Date'[Fiscal Month Number]) > 6, 0.1,
            1
        )
)

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson

     

    Connect with me on LinkedIn

    Check out my Blog

    Going to the European Microsoft Fabric Community Conference? Check out my Session



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

      Hello SamsonTruong,

       

      A Kind note on this measure so that CornelisV  knows, that if its equal 6 then it will be sales amount  *  1, unless you want it to fit to one of the first two conditions then you make it >= or <=.

       

       

    • CornelisV's avatar
      CornelisV
      Icon for Helper IV rankHelper IV
      1 year ago

      Hi SamsonTruong ,

       

      Thank you, the same solution as that from Idrissshatila , with the difference that you are using SWITCH.

      It works very good.

       

      Best regards,

       

      Cornelis