Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
amaleranda
Post Patron
Post Patron

Find total duration based on virtual filtered table.

‎07-18-2019 11:33 PM

I have a difficult situation(to my limited DAX knowledge). Row Data tabel copied below.

 

I have a table like below. What I need is filter this tabel by maximum [Duration] (in this case Type = [Gri]). Then see other two lines( Type = [BL], [Cru] )[Start] and [End] falls within the [Start] and [Finish] of this item( Type =[Gri]). If the [Start] and [End] falls outside of item type = [Gri] then keep those one in the table otherwise exclude. 

[Start] and [End] are times. Data is stored in a minute level granularity. I am putting type in the pivot table column. to get the [Start] and [End] I use MIN and MAX

 

Type      Duration       Start     End

BL           6                  13:13    13:18

Cru         5                   13:00   13:04

Gri        18                    13:13   13:30

 

So final table looks like below since Type [BL] falls within 13:13 and 13:30 time slot exclude that in the final result. I have explore using virtual tables since I only need the total duration of below two, which should be 23.  Could you please assist me on this. Your help is much appriciated.

Type      Duration       Start     End

Cru         5                   13:00   13:04

Gri        18                    13:13   13:30

 

 

 

TimeTypeAreaSubAreaGroup
13:13BLA1SA1G1
13:14BLA1SA1G1
13:15BLA1SA1G1
13:16BLA1SA1G1
13:17BLA1SA1G1
13:18BLA1SA1G1
13:13GriA1SA1G1
13:14GriA1SA1G1
13:15GriA1SA1G1
13:16GriA1SA1G1
13:17GriA1SA1G1
13:18GriA1SA1G1
13:19GriA1SA1G1
13:20GriA1SA1G1
13:21GriA1SA1G1
13:22GriA1SA1G1
13:23GriA1SA1G1
13:24GriA1SA1G1
13:25GriA1SA1G1
13:26GriA1SA1G1
13:27GriA1SA1G1
13:28GriA1SA1G1
13:29GriA1SA1G1
13:30GriA1SA1G1
13:00GriA1SA1G1
13:01CruA1SA1G1
13:02CruA1SA1G1
13:03CruA1SA1G1
13:04CruA1SA1G1
13:19CruA1SA1G1
Message 1 of 4
348 Views
0
2 ACCEPTED SOLUTIONS
amaleranda
Post Patron
Post Patron
In response to v-yuta-msft

‎07-22-2019 01:15 AM

Hi Jimmy,

 

Seems like this could work for me.

 

I have excluded some data in the miniute level granuality for the sake of simplicity. 

 

My actual data looks like below. I need duration to calculate when only Avil column is '0'.

 

This is just a 30min sample only involving only 3 equips only. I have about 150 other type of equips. 

 

What I am trying here is to get the total duration of area where Avil is '0' based on the orginal explenation. I am using Time Brush to change the time in the report. 

 

I can email my PBIX file if you want to have a look.

 

You have been a great help so far.

 

TimeEquipAvilAreaSubAreaGroup
13:00BL1A1SA1G1
13:01BL1A1SA1G1
13:02BL1A1SA1G1
13:03BL1A1SA1G1
13:04BL1A1SA1G1
13:05BL1A1SA1G1
13:06BL1A1SA1G1
13:07BL1A1SA1G1
13:08BL1A1SA1G1
13:09BL1A1SA1G1
13:10BL1A1SA1G1
13:11BL1A1SA1G1
13:12BL1A1SA1G1
13:13BL0A1SA1G1
13:14BL0A1SA1G1
13:15BL0A1SA1G1
13:16BL0A1SA1G1
13:17BL0A1SA1G1
13:18BL0A1SA1G1
13:19BL1A1SA1G1
13:20BL1A1SA1G1
13:21BL1A1SA1G1
13:22BL1A1SA1G1
13:23BL1A1SA1G1
13:24BL1A1SA1G1
13:25BL1A1SA1G1
13:26BL1A1SA1G1
13:27BL1A1SA1G1
13:28BL1A1SA1G1
13:29BL1A1SA1G1
13:30BL1A1SA1G1
13:00Gri1A1SA1G1
13:01Gri1A1SA1G1
13:02Gri1A1SA1G1
13:03Gri1A1SA1G1
13:04Gri1A1SA1G1
13:05Gri1A1SA1G1
13:06Gri1A1SA1G1
13:07Gri1A1SA1G1
13:08Gri1A1SA1G1
13:09Gri1A1SA1G1
13:10Gri1A1SA1G1
13:11Gri1A1SA1G1
13:12Gri1A1SA1G1
13:13Gri0A1SA1G1
13:14Gri0A1SA1G1
13:15Gri0A1SA1G1
13:16Gri0A1SA1G1
13:17Gri0A1SA1G1
13:18Gri0A1SA1G1
13:19Gri0A1SA1G1
13:20Gri0A1SA1G1
13:21Gri0A1SA1G1
13:22Gri0A1SA1G1
13:23Gri0A1SA1G1
13:24Gri0A1SA1G1
13:25Gri0A1SA1G1
13:26Gri0A1SA1G1
13:27Gri0A1SA1G1
13:28Gri0A1SA1G1
13:29Gri0A1SA1G1
13:30Gri0A1SA1G1
13:00Cru0A1SA1G1
13:01Cru0A1SA1G1
13:02Cru0A1SA1G1
13:03Cru0A1SA1G1
13:04Cru0A1SA1G1
13:05Cru1A1SA1G1
13:06Cru1A1SA1G1
13:07Cru1A1SA1G1
13:08Cru1A1SA1G1
13:09Cru1A1SA1G1
13:10Cru1A1SA1G1
13:11Cru1A1SA1G1
13:12Cru1A1SA1G1
13:13Cru1A1SA1G1
13:14Cru1A1SA1G1
13:15Cru1A1SA1G1
13:16Cru1A1SA1G1
13:17Cru1A1SA1G1
13:18Cru1A1SA1G1
13:19Cru0A1SA1G1
13:20Cru1A1SA1G1
13:21Cru1A1SA1G1
13:22Cru1A1SA1G1
13:23Cru1A1SA1G1
13:24Cru1A1SA1G1
13:25Cru1A1SA1G1
13:26Cru1A1SA1G1
13:27Cru1A1SA1G1
13:28Cru1A1SA1G1
13:29Cru1A1SA1G1
13:30Cru1A1SA1G1

View solution in original post

Message 3 of 4
307 Views
0
v-yuta-msft
Community Support
Community Support
In response to amaleranda

‎07-26-2019 12:59 AM

@amaleranda ,

 

You just need to modify the dax of first table like below:

New Table =
SUMMARIZE (
    FILTER ( 'Table', 'Table'[Avil] = 0 ),
    'Table'[Type],
    "Duration", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ),
    "Start", CALCULATE ( MIN ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ),
    "End", CALCULATE ( MAX ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) )
)

Community Support Team _ Jimmy Tao

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

View solution in original post

Message 4 of 4
289 Views
3 REPLIES 3
v-yuta-msft
Community Support
Community Support

‎07-22-2019 12:47 AM

@amaleranda ,

 

You can create two tables using DAX below:

New Table = 
SUMMARIZE (
    'Table',
    'Table'[Type],
    "Duration", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ),
    "Start", CALCULATE ( MIN ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ),
    "End", CALCULATE ( MAX ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) )
)

1.PNG 

Result = 
VAR Gri_Start = CALCULATE(MIN('New Table'[Start]), FILTER('New Table', 'New Table'[Type] = "Gri"))
VAR Gri_End = CALCULATE(MIN('New Table'[End]), FILTER('New Table', 'New Table'[Type] = "Gri"))
RETURN
FILTER('New Table', 'New Table'[Start] < Gri_Start || 'New Table'[End] > Gri_End)

2.PNG 

 

Community Support Team _ Jimmy Tao

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

Find total duration based on virtual filtered table.pbix
Message 2 of 4
313 Views
amaleranda
Post Patron
Post Patron
In response to v-yuta-msft

‎07-22-2019 01:15 AM

Hi Jimmy,

 

Seems like this could work for me.

 

I have excluded some data in the miniute level granuality for the sake of simplicity. 

 

My actual data looks like below. I need duration to calculate when only Avil column is '0'.

 

This is just a 30min sample only involving only 3 equips only. I have about 150 other type of equips. 

 

What I am trying here is to get the total duration of area where Avil is '0' based on the orginal explenation. I am using Time Brush to change the time in the report. 

 

I can email my PBIX file if you want to have a look.

 

You have been a great help so far.

 

TimeEquipAvilAreaSubAreaGroup
13:00BL1A1SA1G1
13:01BL1A1SA1G1
13:02BL1A1SA1G1
13:03BL1A1SA1G1
13:04BL1A1SA1G1
13:05BL1A1SA1G1
13:06BL1A1SA1G1
13:07BL1A1SA1G1
13:08BL1A1SA1G1
13:09BL1A1SA1G1
13:10BL1A1SA1G1
13:11BL1A1SA1G1
13:12BL1A1SA1G1
13:13BL0A1SA1G1
13:14BL0A1SA1G1
13:15BL0A1SA1G1
13:16BL0A1SA1G1
13:17BL0A1SA1G1
13:18BL0A1SA1G1
13:19BL1A1SA1G1
13:20BL1A1SA1G1
13:21BL1A1SA1G1
13:22BL1A1SA1G1
13:23BL1A1SA1G1
13:24BL1A1SA1G1
13:25BL1A1SA1G1
13:26BL1A1SA1G1
13:27BL1A1SA1G1
13:28BL1A1SA1G1
13:29BL1A1SA1G1
13:30BL1A1SA1G1
13:00Gri1A1SA1G1
13:01Gri1A1SA1G1
13:02Gri1A1SA1G1
13:03Gri1A1SA1G1
13:04Gri1A1SA1G1
13:05Gri1A1SA1G1
13:06Gri1A1SA1G1
13:07Gri1A1SA1G1
13:08Gri1A1SA1G1
13:09Gri1A1SA1G1
13:10Gri1A1SA1G1
13:11Gri1A1SA1G1
13:12Gri1A1SA1G1
13:13Gri0A1SA1G1
13:14Gri0A1SA1G1
13:15Gri0A1SA1G1
13:16Gri0A1SA1G1
13:17Gri0A1SA1G1
13:18Gri0A1SA1G1
13:19Gri0A1SA1G1
13:20Gri0A1SA1G1
13:21Gri0A1SA1G1
13:22Gri0A1SA1G1
13:23Gri0A1SA1G1
13:24Gri0A1SA1G1
13:25Gri0A1SA1G1
13:26Gri0A1SA1G1
13:27Gri0A1SA1G1
13:28Gri0A1SA1G1
13:29Gri0A1SA1G1
13:30Gri0A1SA1G1
13:00Cru0A1SA1G1
13:01Cru0A1SA1G1
13:02Cru0A1SA1G1
13:03Cru0A1SA1G1
13:04Cru0A1SA1G1
13:05Cru1A1SA1G1
13:06Cru1A1SA1G1
13:07Cru1A1SA1G1
13:08Cru1A1SA1G1
13:09Cru1A1SA1G1
13:10Cru1A1SA1G1
13:11Cru1A1SA1G1
13:12Cru1A1SA1G1
13:13Cru1A1SA1G1
13:14Cru1A1SA1G1
13:15Cru1A1SA1G1
13:16Cru1A1SA1G1
13:17Cru1A1SA1G1
13:18Cru1A1SA1G1
13:19Cru0A1SA1G1
13:20Cru1A1SA1G1
13:21Cru1A1SA1G1
13:22Cru1A1SA1G1
13:23Cru1A1SA1G1
13:24Cru1A1SA1G1
13:25Cru1A1SA1G1
13:26Cru1A1SA1G1
13:27Cru1A1SA1G1
13:28Cru1A1SA1G1
13:29Cru1A1SA1G1
13:30Cru1A1SA1G1
Message 3 of 4
308 Views
0
v-yuta-msft
Community Support
Community Support
In response to amaleranda

‎07-26-2019 12:59 AM

@amaleranda ,

 

You just need to modify the dax of first table like below:

New Table =
SUMMARIZE (
    FILTER ( 'Table', 'Table'[Avil] = 0 ),
    'Table'[Type],
    "Duration", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ),
    "Start", CALCULATE ( MIN ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ),
    "End", CALCULATE ( MAX ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) )
)

Community Support Team _ Jimmy Tao

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

Message 4 of 4
290 Views

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All