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
87Happy
Regular Visitor

Cross Table Calculations

Good Day

 

I have two challenges which I’m looking for help to resolve.

 

The first is with a calculation, the latter is how I can accomplish the visual.

 

Details on the calculation.

I have a table with a list of customer released trailers (Table 1 called Release).  I’m currently comparing it to a list of customer requested empty trailers (Table 2 called Empty) to find any orders which were open during the time the empty release was made.

For instance, a 53’ Trailer is released at company A at 8am and it needs to be picked up before 4pm. (Table 1 contains these details).

The filters I’m currently running within Table 1 count the # of rows in Table 2 which match with city, trailer size and delivery appointment time.

 

For this example there are 3 rows which match (Point 1, Point 2 and Point 3).  I’m stuck, on the part where I need to check the rate for each of these matches and output the lowest cost.

Currently running Min or Max on the filter based on the Destination point, but I want to do it based on the result.

 

ORIGIN POINT 1 in Table 1 with DESTINATION POINT 1 in Table 2, checking Table 3 for the Rate – Result is $30

ORIGIN POINT 1 in Table 1 with DESTINATION POINT 2 in Table 2, checking Table 3 for the Rate – Result is $40

ORIGIN POINT 1 in Table 1 with DESTINATION POINT 3 in Table 2, checking Table 3 for the Rate – Result is $20

 

Result should be the lowest cost - $20. 

 

How can I accomplish this? 

 

Additionally, I’m trying to keep in perspective that when it comes to the Visuals, I want two interactive tables (Table 1 Releases & Table 2 Empties).

Click on a Row in Table 1 Releases and it will update Table 2 to show the rows which match the criteria with details including the potential cost.

So for this example, click on Company A’s Row in Table 1, and see all 3 rows in the second table showing $30, $40 & $20 related to each row. 

Highlighting the row of $20 since it’s the lowest cost.

 

Table 1 (Release)

Table 2 (Empty)

Table 3 (Rate)

 

Sample file included if you follow the link below.

 

Thanks in Advance!

5 REPLIES 5
v-jialluo-msft
Community Support
Community Support

Hi  @87Happy ,

 

Do you use the FLAG function to filter? The specific usage method is as follows:

(1)Create a  measure

FLAG = 
VAR _CITY = MAX('Table 1 - Release'[CITY])
VAR _SIZE = MAX('Table 1 - Release'[TRAILER SIZE])
VAR _START = MAX('Table 1 - Release'[START OF APPT])
VAR _END = MAX('Table 1 - Release'[END OF APPT])
RETURN IF(MAX('Table 2 - Empty'[CITY])=_CITY && MAX('Table 2 - Empty'[TRAILER SIZE])=_SIZE && MAX('Table 2 - Empty'[START OF APPT])>=_START && MAX('Table 2 - Empty'[END OF APPT])<=_END,1,0)

(2)Apply filtering

vjialluomsft_0-1670219245935.png

I included this function in the file I replied to last time, if this doesn't solve your question, please upload the sample file. I was able to filter out the values normally in the previous example file

 

Best Regards,
Gallen Luo

v-jialluo-msft
Community Support
Community Support

Hi @87Happy ,

 

Please follow these steps:

(1) Create a new measure

RATES =

VAR _O =

    MAX ( 'Table 1 - Release'[ZONE] )

VAR _C =

    MAX ( 'Table 1 - Release'[CITY] )

VAR _D =

    MAX ( 'Table 2 - Empty'[ZONE] )

RETURN

    MAXX (

        FILTER (

            ALL ( 'Table 3 - Rates' ),

            'Table 3 - Rates'[ORIGIN] = _O

                && 'Table 3 - Rates'[DESTINATION] = _D

                && 'Table 3 - Rates'[CITY] = _C

        ),

        'Table 3 - Rates'[RATE]

    )

 

 

(2)Final output

vjialluomsft_0-1669888446287.png

 

Best Regards,

Gallen Luo

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

It's giving me an error that for a single value for column 'Table 2 - Empty [Zone] cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.

 

This ties to my problem that an order has one city, one origin point but could have many different destination points.  I need to run the calculation for the # of matching destination points and output just the cheapest option.  But as noted, factor in that I'll need to include in the visual a representation.

 

If I was doing this with another language, I'd run a For loop for the # of matching orders and output each calculation into a variable, checking each time whether the new output is smaller than the last output.  if it is, keep it, if not discard.  Then the final output would be that variable's final result.  

 

Is that feasible in Power BI?

 

v-jialluo-msft
Community Support
Community Support

Hi @87Happy ,

 

According to your description, the problem is to filter Table 2 through Table 1, and then query the rate according to the origin of Table 1 and the destination filtered out by Table 2, and highlight the minimum rate.

But none of your three tables have primary keys, which makes it difficult to filter the data, and I don't get the data you described, the rate from origin 1 to destination 1 is not equal to 30.

If my understanding is wrong, I hope to be able to point it out.

vjialluomsft_0-1669803087581.png

 

Best Regards,

Gallen Luo

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

I've updated the data to be more accurate to my written example but you're explanation is correct.  I'm familiar with the concept of Primary Keys but don't have alot of practice with them or understand how it would help in this situation.  Looking forward to your insite.

 

https://drive.google.com/file/d/1TEcgqZ7sNluBz8hCGKYfSslXrgJuxML_/view?usp=share_link

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.