Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
THENNA_41
Post Partisan
Post Partisan

Excel calculations covert to dax

DZ          EA                  EB
 
 
TIME       PAX               PEAK PAX   - excel header name 
 
02:00                               #N/A         (EA start from E12  in Excel)                
02:15                               #N/A
02:30                               #N/A
02:45                               #N/A
03:00                               #N/A
03:15                               #N/A
03:30                               #N/A
03:45      1                       #N/A
04:00     14                     #N/A
04:15     53                     #N/A
04:30    124                    #N/A
04:45    206                   #N/A
05:00    266                  #N/A
05:15    289                   289
05:30   289                     #N/A
05:45   280                    #N/A
06:00   274                     #N/A
06:15   273              -       #N/A
06:30   290                    #N/A
06:45   341                     #N/A
07:00   432                    #N/A
07:15   542                       #N/A
07:30  647                     #N/A
07:45  739                 #N/A
 
 
for above PEAK PAX CALCUALTIONS excel used the below calcaultions 
=IF(AND(EA12>EA11,EA12>=EA13),EA12,NA())
 
I want this calcations in  DAX . please help me . looking for support ..
2 REPLIES 2
123abc
Community Champion
Community Champion

Certainly! In DAX (Data Analysis Expressions), you can achieve the equivalent calculation using the following formula:

 

PEAK PAX = IF(AND(EA[TIME] > EARLIER(EA[TIME]), EA[TIME] >= NEXTDAY(EA[TIME])), EA[PAX])

 

Here, I assume that your table is named "EA" and has columns "TIME," "PAX," and "PEAK PAX." The EARLIER function is used to refer to the previous row in the table, and NEXTDAY is used to get the next row. The condition checks if the current row's time is greater than the previous row's time and greater than or equal to the next row's time. If this condition is met, it returns the value of "PAX," otherwise, it returns blank.

Please adjust the table and column names in the formula based on your actual data model.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

FreemanZ
Super User
Super User

Hi @THENNA_41 ,

 

try like:

 

Column = 
VAR _p = [pax]
VAR _pre = 
MAXX(
    TOPN(
        1, 
        FILTER(
            data, 
            data[time]< EARLIER(data[time])
        ),  
        data[time]
    ), 
    data[pax]
)
VAR _next = 
MAXX(
    TOPN(
        1, 
        FILTER(
            data, 
            data[time]> EARLIER(data[time])
        ),  
        data[time]
    ), 
    data[pax]
)
VAR _result = IF(_p>_pre && _p>=_next, _p)
RETURN _result

 

 

it worked like:

FreemanZ_0-1700460916653.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors