Forum Discussion

ozgeozkaya's avatar
ozgeozkaya
Regular Visitor
3 years ago

Additional condition for multiple IF statement

I have a data set which includes consultation numbers of doctors per day and I am trying to calculate their pay share for the selected month. From the date slicer, first I choose a specific year and month.

 

I created the below DAX measure to calculate the share. Consultations[Consultations] is the measure that sums up the number of consultations. It has more levels but for ths sake of simplicity I only shared the first two levels. For instance, if the doctor has 110 consultations in that month then the share will be 2000 +10*25 = 2250 USD

 

Share = IF(Consultations[Consultations] <= 100 , 20*Consultations[Consultations],
IF(Consultations[Consultations] > 100 && Consultations[Consultations] <= 200 , 2000 + 25*(Consultations[Consultations]-100)
))

 

My challange here is to include an additional condition for a specific column. 
Consultations[Code] = "234"
I tried to add the additional condition like below however it does not do the calculation correctly. Any suggestions please?

 

Share = IF(Consultations[Consultations] <= 100 && max(Consultations[Code])= "234" , 20*Consultations[Consultations],
IF(Consultations[Consultations] > 100 && Consultations[Consultations] <= 200 && max(Consultations[Code])= "234", 2000 + 25*(Consultations[Consultations]-100)
))

 

 

3 Replies

  • Martin_D's avatar
    Martin_D
    Solution Sage
    3 years ago

    Hi ozgeozkaya ,

    Without having a sample file and a description what calcualtion is actually done if not the indended one, it's just guessing what the problem might be.

    What I can see in your code is that you treat the Consultations[Code] as text. So if there is a consultation code like e.g. "55" then this is higher than "234" (first character is 5, 5 is higher than 2). It might help to turn the datatype of Consultations[Code] into whole number.
    BR
    Martin

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  ozgeozkaya ,

    If you are using Measure, IF requires adding Max to the front of the column to identify the current value:

    Share =
IF (
    MAX ( Consultations[Consultations] ) <= 100
       && MAX ( Consultations[Code] ) = "234",
    20 * Consultations[Consultations],
    IF (
        MAX ( Consultations[Consultations] ) > 100
            && MAX ( Consultations[Consultations] ) <= 200
            && MAX ( Consultations[Code] ) = "234",
        2000 + 25 * ( Consultations[Consultations] - 100 )
    )
)

    If I have misunderstood your meaning, please provide more details about your desired output.

     

    Best Regards,

    Liu Yang

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

  • ozge_ozkaya's avatar
    ozge_ozkaya
    New Member
    3 years ago

    Thank you for the replies. I managed to solve the issue by creating another measure and filter it.


    My original measure that makes the calculation:

    Share = IF(Consultations[Consultations] <= 100 , 20*Consultations[Consultations],
IF(Consultations[Consultations] > 100 && Consultations[Consultations] <= 200 , 2000 + 25*(Consultations[Consultations]-100)
))

     

    Here is my second measure that filters it:

    Calculate Share = CALCULATE([Share],FILTER(Consultations,Consultations[Code] = "234"))

     

    Warm wishes