Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee 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
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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 🙂
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi Ed,
really appreciate your help, it works now 😄
Hi Ed,
can you please check my DAX, it gives me error. thanks a lot.
You need a comma here.
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!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCan 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 🙂
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |