cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

## How to find Estimate (PAX PER 15 MINUTES) from Table A to Table B ..

I want fetch value from Table A to Table B for ESTIMATE PER 15 MINUTES. I have attached Sample Data for reference . also attached Excel calcautlions for reference as  well

Below Excel formula used for calculations

=SUMIFS(AY:AY,AV:AV,">="&\$BQ28,AV:AV,"<"&\$BQ29)

AY - STD , AV - ESTIMATE , BQ Column - TIME (Table B)

TABLE A :

AV        AW          AX               AY                                           --- Excel column

STD    FLIGH   T AIRPORT     ESTIMATE
06:00  QF402      SYD            142
06:05  QF765      PER            145
07:00   QF410    SYD          132
07:05   QF604    BNE          126
08:00   QF418   SYD           148
08:05   QF1514  CBR            83
08:25  QF1551  HBA          109
08:30 QF2051   DPO         36

TABLE B :

BQ                BR                                           -- Excel Column

TIME-           PAX  ( Output column in EXCEL)

03:00
03:15
03:30
03:45
04:00
04:15
04:30
04:45
05:00
05:15
05:30
05:45
06:00    287 -- 142+145 6 to 6.15
06:15
06:30
06:45
07:00     258 -- 132+126 7 to 7.15
07:15
07:30
07:45
08:00    231 -- 148+83 8 to 8.15
08:15
08:30    145 --109+36 8.15 to 8.30

Converting this  data in to power bi .  How to convert this formula in Dax ? looking for support .. thanks in advance

1 ACCEPTED SOLUTION
Super User

Hi @THENNA_41
What is "CX" in 'TABLE A'[ESTIMATE]?

``````PAX =
VAR CurrentTime = 'TABLE B'[TIME]
VAR TimesAfter = FILTER ( VALUES ( 'TABLE B'[TIME] ), 'TABLE B'[TIME] > CurrentTime )
VAR NextTime = MINX ( TimesAfter, 'TABLE B'[TIME] )
VAR FiteredTable = FILTER ( 'TABLE A', 'TABLE A'[STD] >= CurrentTime && 'TABLE A'[STD] < NextTime )
VAR Result =
SUMX (
FiteredTable,
IFERROR ( VALUE ( 'TABLE A'[ESTIMATE] ), BLANK ( ) )
)
RETURN
Result``````

Super User

Hi @THENNA_41
What is "CX" in 'TABLE A'[ESTIMATE]?

``````PAX =
VAR CurrentTime = 'TABLE B'[TIME]
VAR TimesAfter = FILTER ( VALUES ( 'TABLE B'[TIME] ), 'TABLE B'[TIME] > CurrentTime )
VAR NextTime = MINX ( TimesAfter, 'TABLE B'[TIME] )
VAR FiteredTable = FILTER ( 'TABLE A', 'TABLE A'[STD] >= CurrentTime && 'TABLE A'[STD] < NextTime )
VAR Result =
SUMX (
FiteredTable,
IFERROR ( VALUE ( 'TABLE A'[ESTIMATE] ), BLANK ( ) )
)
RETURN
Result``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors