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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yiling
Helper II
Helper II

DAX - Classify first train service on each block on a daily basis

Hi Experts, hope you guys can help me on this.

 

I try to identify whether is this train service is the first departing service on its block on a daily basis, if yes is 1, else is 0 (in power bi desktop) 

i can think of using mini to identify whether is the first departing service, but putting all the criteria together, can't make it work

Background: trains will leave from different yards and return back to (not neccessarily the same yards) at the end of the day. 

during the day trains run on diffect block. 

TT_TDN is train service number

TT_Block is block number 

 

would appreciate if some one can help me 

 

Yiling_0-1602633697660.png

 

 

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

See if this works as a calculated column for you @Yiling (EDIT: removed ALL() - you don't need that for a calculated column here as shown in the image.

 

First Departure = 
VAR varDate = Trains[OPERATING_DATE]
VAR varFirstTime =
    MINX(
        FILTER(
            Trains,
            Trains[OPERATING_DATE] = varDate
        ),
        Trains[TT_TIME_FROM]
    )
RETURN
    IF(
        Trains[TT_TIME_FROM] = varFirstTime
            && Trains[OPERATING_DATE] = varDate,
        1,
        0
    )

 

edhans_0-1602641431149.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

12 REPLIES 12
edhans
Super User
Super User

See if this works as a calculated column for you @Yiling (EDIT: removed ALL() - you don't need that for a calculated column here as shown in the image.

 

First Departure = 
VAR varDate = Trains[OPERATING_DATE]
VAR varFirstTime =
    MINX(
        FILTER(
            Trains,
            Trains[OPERATING_DATE] = varDate
        ),
        Trains[TT_TIME_FROM]
    )
RETURN
    IF(
        Trains[TT_TIME_FROM] = varFirstTime
            && Trains[OPERATING_DATE] = varDate,
        1,
        0
    )

 

edhans_0-1602641431149.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi Ed @edhans

May i ask you one more question, i know the DAX formula works in power BI, import database and add new column, but ideally i wanna direct query from the database because due to large dataset, so i need to write same in power query editor this environment, see screenshot, i did copy paste, but doesn't work, can you please help me again for this? much appreciate 🙂

Yiling_0-1602713191986.png

First Departure.JPG

Hi @Yiling, I am going to go out on a limb here and say this cannot be done in Power Query if you are connecting by Direct Query. I would need the data in a SQL table to test it, but I do not think the necessary grouping and extracting the min/max values can be folded by Power Query back to the SQL server. I can definitely do this in Power Query, and that was my first thought, but you specifically said you wanted a calculated column, and I know for a fact the DAX in a calculated column for this would be easier to edit and maintain than the M code as you cannot do this through the UI. You have to write some more complex M using nested tables. 

 

I just tested this with another bit of data I am working with - wanted the sum by item and used List.Sum to get it. In your case it would be List.Min, or Table.Min depending on the direction I went. this is the code:

let
    Source = Sql.Database("server", "database"),
    dbo_IMINVLOC_SQL = Source{[Schema="dbo",Item="IMINVLOC_SQL"]}[Data],
    SummedData = 
        Table.AddColumn(
            dbo_IMINVLOC_SQL,
            "Total Quantity",
            each
                let
                    varItem = [item_no]
                in
                List.Sum(
                    Table.SelectRows(
                        dbo_IMINVLOC_SQL,
                        each [item_no] = varItem
                    )[qty_on_hand]
                )
        )
in
    SummedData

The code works, but it will not fold:

edhans_0-1602715437116.png

On an Import model, that is ok, it just means the desktop, service, or on-prem gateway does the work. In Direct Query though, it means it will not work at all.

 

Direct Query is best for massive data that is rapidly changing. Remember even with a Pro license, you can have a 1GB model and that data is compressed so your data would really be larger, possibly much larger.

 

Plus, DQ has so many other limitations in DAX and visualizations. See here for the limitations.

If you really really want to do this via Direct Query, work with your DBA to create a view that will add this 1/0 column for you. I am not a SQL guru by any means, but I am 100% confident someone good with SQL can provide that column for you in a View, then you can connect to the view using Direct Query.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi Ed,

really appreciate your help, it works now 😄

Hi Ed,

can you please check my DAX, it gives me error. thanks a lot.

 

Capture.PNG

 

 

 

 

You need a comma here.

edhans_0-1602689685489.png

If you have this working now @Yiling can you please mark the post that solved it as the solution so this thread can be marked as solved? Thanks!

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Megha166
Employee
Employee

Can you post the Excel File also with Data So I can write the DAX expression for it directly.

Hi Megha,

 

here's the link of the sample in onedrive.

 

https://1drv.ms/x/s!AjpB2wk5YDY3hwCVAPTennVc6Wr_?e=WfYuXh

 

let me know if you can access to, appreciate 🙂 

 

edhans
Super User
Super User

Can you post data in a usable format @Yiling using the links below for guidance? Also, explain the data a bit more for me and show some of the expected output.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi Ed,

 

I have add onedrive link https://1drv.ms/x/s!AjpB2wk5YDY3hwCVAPTennVc6Wr_?e=2Pnrmf,

you can find attachment in the folder, let me know if doesn't work.

 

i download backend table from SQL, planing to create live connect to power bi, and then add DAX formula 

my goal is to identify first departing TT_TDN  on different TT_Block on a different date if yes then 1, the rest are 0,

trying to understand first TT_TDN service departure time whether is on time.

 

 

Thanks for the file. I have it @Yiling 

 

But I don't understand the goal. Remember, I know nothing about trains. What is the answer to the first one and why? Explain it so we can understand the logic to be able to write the necessary code.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi Ed,

 

background

we run trains by train drivers, if the first train leaves yard late, it will impact the following train services late during the day. that's why the first train departure on time is critical to our business.

 

first step of my reporting goal is to identify the service whether is the first train service on each block everyday, (our trains runs on different block everyday, not always run on the same block), 

 ex - M200 is the first service on block 101, departure time is 050900

 

i wanna add a new column tells yes to first service is 1, the rest are 0. 

Yiling_0-1602638996008.png

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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