Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi PBI Community,
i have the following problem and i really don't know how to solve it:
I have for example a following table with processes:
process | start | end |
1 | 22.01.2018 23:00 | 22.01.2018 13:00 |
2 | 23.01.2018 00:00 | 22.01.2018 14:00 |
3 | 23.01.2018 01:00 | 22.01.2018 15:00 |
4 | 23.01.2018 02:00 | 22.01.2018 13:00 |
1 | 23.01.2018 23:00 | 23.01.2018 13:00 |
2 | 24.01.2018 00:00 | 23.01.2018 14:00 |
3 | 24.01.2018 01:00 | 23.01.2018 15:00
|
4 | 24.01.2018 02:00 | 23.01.2018 13:00 |
- Each of them has start and end time
What I would like to know out of this data with DAX:
For each specific day f.e. the 22.01 what was the minimum start timestamp on that day and what was the max end timestamp for one day? And another assumption each process has a attribut like region attached than my question woul be: what is the minimum starting time for each process per day per region.
I hope you can help me! Thanks a lot and a great day to all of you
Solved! Go to Solution.
Hi,
>>> For each specific day f.e. the 22.01 what was the minimum start timestamp on that day and what was the max end timestamp for one day?
To accomplish this, you could split the column “start” into “startdate” and “starttime”, similarly, split “end” into “enddate” and “endtime”. You can do this in “Edit Query”>> Split Column >> by number of characters (10) .
Then add two measures :
min_timestamp_in_one_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[startdate]))
max_timestamp_in_one_day = CALCULATE(MAXX(Table1,Table1[endtime]),ALLEXCEPT(Table1,Table1[enddate]))
Noted you need to manually change the datatype format for all the relative columns and above two measures.
Then you will get result like:
>>> what is the minimum starting time for each process per day per region.
So what result would you like to see if we are dealing with the table you post here?
For example, I have a datasource like:
If you want to calculate the earliest timestamp for a region, you could use DAX like:
Earliest_time_per_region = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]))
And you could add the column in the “ALLEXCEPT” function as you wish. For example, you could also use:
Earliest_time_per_region_per_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]),Table1[startdate])
BR,
Henry
Hi PBI Community,
i have the following problem. Lets assume following table of processes with start and end times:
process | start | end |
1 | 22.01.2018 23:00 | 22.01.2018 13:00 |
2 | 23.01.2018 00:00 | 22.01.2018 14:00 |
3 | 23.01.2018 01:00 | 22.01.2018 15:00 |
4 | 23.01.2018 02:00 | 22.01.2018 13:00 |
1 | 23.01.2018 23:00 | 23.01.2018 13:00 |
2 | 24.01.2018 00:00 | 23.01.2018 14:00 |
3 | 24.01.2018 01:00 | 23.01.2018 15:00 |
4 | 24.01.2018 02:00 | 23.01.2018 13:00 |
What I would like to know out of this table is for each day the minimum start time of a process and for each day the maximum end timestamp of a process. Lets assume also another attribute called region on each process. What is the minum starting time per day for each region?
I hope someone can help me.
Thanks,
Sandra
Hi,
>>> For each specific day f.e. the 22.01 what was the minimum start timestamp on that day and what was the max end timestamp for one day?
To accomplish this, you could split the column “start” into “startdate” and “starttime”, similarly, split “end” into “enddate” and “endtime”. You can do this in “Edit Query”>> Split Column >> by number of characters (10) .
Then add two measures :
min_timestamp_in_one_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[startdate]))
max_timestamp_in_one_day = CALCULATE(MAXX(Table1,Table1[endtime]),ALLEXCEPT(Table1,Table1[enddate]))
Noted you need to manually change the datatype format for all the relative columns and above two measures.
Then you will get result like:
>>> what is the minimum starting time for each process per day per region.
So what result would you like to see if we are dealing with the table you post here?
For example, I have a datasource like:
If you want to calculate the earliest timestamp for a region, you could use DAX like:
Earliest_time_per_region = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]))
And you could add the column in the “ALLEXCEPT” function as you wish. For example, you could also use:
Earliest_time_per_region_per_day = CALCULATE(MINX(Table1,Table1[starttime]),ALLEXCEPT(Table1,Table1[Region]),Table1[startdate])
BR,
Henry
Thanks a lot!! 🙂 That's exactly what I needed 🙂
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |