How to write my excel formula in DAX

[Masoud_]

Hi every one,

I have a formula in Excel and I am trying to write it in DAX but I am new and I don't know how to do that.

The code is not complicated, but I am struggelling a few days to solve it.

=IF(OR((C5-C4)>0,(C4-C3)>0),1,IF(AND((C3-C2)>0,(C4-C5)>-0.05),1,0))

Does anyone knows how to do that?

[Anonymous · Accepted solution]

Hi @Masoud_ ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column = 
VAR _cur = 'Table'[Celsius]
VAR _cur1 =
    CALCULATE (
        MAX ( 'Table'[Celsius] ),
        FILTER ( 'Table', 'Table'[Temp#55] = EARLIER ( 'Table'[Temp#55] ) + 1 )
    )
VAR _cur2 =
    CALCULATE (
        MAX ( 'Table'[Celsius] ),
        FILTER ( 'Table', 'Table'[Temp#55] = EARLIER ( 'Table'[Temp#55] ) + 2 )
    )
VAR _cur3 =
    CALCULATE (
        MAX ( 'Table'[Celsius] ),
        FILTER ( 'Table', 'Table'[Temp#55] = EARLIER ( 'Table'[Temp#55] ) + 3 )
    )
VAR _cur4 =
    CALCULATE (
        MAX ( 'Table'[Celsius] ),
        FILTER ( 'Table', 'Table'[Temp#55] = EARLIER ( 'Table'[Temp#55] ) + 4 )
    )
RETURN
    IF (
        _cur4 - _cur3 > 0
            || _cur3 - _cur2 > 0,
        1,
        IF ( _cur2 - _cur1 > 0 && _cur3 - _cur4 > -0.05, 1, 0 )
    )

If the above one can't help you get the desired result, please provide some sample data in the table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[Masoud_]

@Anonymous 

Thank you for your answer. It worked perfectly.

I appreciate your time and effort.

Kind Regards,

Masoud

[Ashish_Mathur]

Hi,

While in cell G2, why should we refer to cells C5 and C4?  What is the logic? 

[Masoud_]

So, it's a formula based on time period data and it needs 4 rows at a time to calculate a result.

Columns D, E and F are not concerned with this formula.

[Ashish_Mathur]

Hi,

Please state the rules very clearly and also show the expected result.

[Masoud_]

   The concept of formula is like this:

if (T(t)-T(t-1)>0) or (if T(t-1)-T(t-2) > 0
or T(t-2) - T (t-3) >0 and T (t-1) - T (t) > -0.05)

Result should be like this:

[lbendlin]

Don't try to convert Excel concepts into Power BI. Instead, please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Masoud_]

TimeCelsius(°C)Humidity(%rh)Dew Point(°C)Formula1

12/11/2021 12:00	18	74.5	13.4	0
12/11/2021 12:10	18	75	13.5	0
12/11/2021 12:20	18	75	13.5	0
12/11/2021 12:30	18	75	13.5	0
12/11/2021 12:40	18	75	13.5	0
12/11/2021 12:50	18	75	13.5	0
12/11/2021 13:00	18	75	13.5	0
12/11/2021 13:10	18	75	13.5	0
12/11/2021 13:20	18	75.5	13.6	0

Data sample is like this table and formula is =IF(OR((C5-C4)>0,(C4-C3)>0),1,IF(AND((C3-C2)>0,(C4-C5)>-0.05),1,0)).

The result is 0 and 1.

If I do not use power BI coding to calculate my formulas, how should I avoid generating too much-recorded data in the same worksheet? Also, my data are separated from case studies but need to process the same, and it takes a lot of space and human effort if I want to do all my analysis in Excel!