Help with Multiple Table Measures with and without Aggregate

[MRGMSR]

Hi,

I have been asked to develop a dashboard that shows data for different services when Month & Year is selected from a drop-down menu. The data comes from 7 different fact tables. I have connected all fact tables to one CALENDAR TABLE on Month & Year column and I created a Services table and connected the same 7 tables to Services.

All works well until I try to bring data from two different tables into one measure. 

TABLE1 holds MBRC for all Services (Rail, Bus, BRT, etc.)  by Route (multiple routes for 1 service) there is a 0 value for Rail which is its own route because that data is stored in a spreadsheet (TABLE2)

TABLE2 holds MBRC for 1 Service (Rail) by Month

(I can't set a relationship with these two tables because I get an error stating there would be issues between TABLE1 and SERVICES if I set up a relationship between TABLE1 and TABLE2)

I created a measure to provide MBRC for a SERVICE using SELECTEDVALUE (SERVICES) and SWITCH (AVERAGE(MBRC) by service. I cannot create a measure to provide MBRC for Rail because to bring it into the measure (above), I have to use an aggregate that throws off the correct MBRC because if I use SUM, it adds the MBRC column and provides a total. If I use AVERAGE, it averages the total column and provides that value regardless of the month and year I choose from the drop-down menu.

Here is a sample of the data

TABLE1

MONTH_SHORT_NAME	MONTH_END_DATE	MONTH_NAME	MONTH_BEGIN_DATE	MONTH_YEAR_NAME	CALENDAR_MONTH_NUMBER	CALENDAR_YEAR_NUMBER	ROUTE_SORT_ORDER	RELIABILITY_GOAL	FISCAL_YEAR_NUMBER	FISCAL_MONTH_NUMBER	MODE_NAME	ROUTE_NAME	INCIDENT_NAME	INCIDENTS	TOTAL_INCIDENTS	OTP_PCT	YTD_OTP_PCT	QTD_OTP_PCT	MEAN_MILES_BETWEEN_ROAD_CALLS	DATA_AS_OF	OTP_Month_Year	DateKey	1_
Aug	########	August	Sunday, August 1, 2021	20-Aug	8	2021	2	8300.00%	2021	11	MetroBus	1-North Lamar/South Congress	EARLY	1541	26395	583.00%	4.63	5.71	3415.783	########	Aug-21	20210801	16042
Aug	########	August	Sunday, August 1, 2021	20-Aug	8	2021	9	8300.00%	2021	11	MetroBus	10-South 1st/Red River	EARLY	1413	23582	599.00%	4.56	5.89	3415.783	########	Aug-21	20210801	16042
Aug	########	August	Sunday, August 1, 2021	20-Aug	8	2021	22	8300.00%	2021	11	MetroBus	103-Manchaca Flyer	EARLY	28	321	872.00%	7.62	7.37	3415.783	########	Aug-21	20210801	16042
Aug	########	August	Sunday, August 1, 2021	20-Aug	8	2021	25	8300.00%	2021	11	MetroBus	111-South Mopac Flyer	EARLY	12	326	368.00%	4.43	3.27	3415.783	########	Aug-21	20210801	16042
Aug	########	August	Sunday, August 1, 2021	20-Aug	8	2021	23	8300.00%	2021	11	MetroBus	105-South 5th Flyer	EARLY	13	330	393.00%	1.84	2.54	3415.783	########	Aug-21	20210801	16042
Aug	########	August	Sunday, August 1, 2021	20-Aug	8	2021	28	8300.00%	2021	11	MetroBus	135-Dell Limited	EARLY	5	164	304.00%	2.53	2.99	3415.783	########	Aug-21	20210801	16042

 TABLE2

MonthMDBFRail Month_YearDateKeyRoute_IDService

December 2020	14191	December 2020	20201220	550	MetroRail
January 2021	25379	January 2021	20210121	550	MetroRail
February 2021	9976	February 2021	20210221	550	MetroRail
March 2021	10198	March 2021	20210321	550	MetroRail
April 2021	29263	April 2021	20210421	550	MetroRail
May 2021	14179	May 2021	20210521	550	MetroRail
June 2021	5930	June 2021	20210621	550	MetroRail
July 2021	16500	July 2021	20210721	550	MetroRail
August 2021	16042	August 2021	20210821	550	MetroRail
September 2021	38583	September 2021	20210921	550	MetroRail
October 2021	19580	October 2021	20211021	550	MetroRail
November 2021	34612	November 2021	20211121	550	MetroRail
December 2021	37431	December 2021	20211221	550	MetroRail
January 2022	8655	January 2022	20220121	550	MetroRail
February 2022	7831	February 2022	20220221	550	MetroRail
March 2022	8811	March 2022	20220321	550	MetroRail
April 2022	9729	April 2022	20220421	550	MetroRail
May 2022	18862	May 2022	20220501	550	MetroRail

I am completely stumped, I have been at it since last night. I really could use a lot of help, I need to get this dashboard done by Tuesday and I have no idea what I am doing 😞

Thanks in advance for any help you can provide.

[MRGMSR]

Hi, thanks for responding. I'm working on a better explanation to share in a bit 

[daXtreme]

Hi @MRGMSR 

If something is hard in PBI, it's a sure sign that the data model is not up to the mark. After reading the description I must say I'm none the wiser. It might be me or it might be the description that's the problem here. Could you please maybe rephrase the descripion? A picture is worth a thousand words as you know 🙂

"All works well until I try to bring data from two different tables into one measure. "

Can you please show what happens when you want to bring data from 2 different tables into one measure? For the time being nobody would probably be able to tell you what is wrong and where... Not enough data.

Also, it would do us all good if you could streamline the model to what's really absolutely necessary to demonstrate the issue and then... well, demonstrate it here.

Thanks.

[MRGMSR]

Hopefully, I can explain the issue. It is frustrating me so much that I can't even explain it...

TABLE1 shows Miles Between Road Calls (MBRC) for each route (multiple rows) for each Service by month except for Rail because that MBRC is in another table (TABLE2) that shows the MBRC by Month (one single row for each month). In TABLE1 Rail shows MBRC as 0

To get the MBRC for each service in TABLE1, I average Miles Between Road Calls of all routes for the Service in a measure AVERAGE(Mean Miles between Road Calls) and I get an average for that service

I put this measure into a SELECTEDVALUE and Switch so when a service is selected in the slicer, I get the average for that service – Works fine for all services except Rail because of the aggregate I need to use

SELECTEDVALUE(Service), SWITCH(“MetroBus", AVERAGE MBRC)

                                                       "MetroRail", AVERAGE(TABLE2)

CORRECT

INCORRECT

TABLE2 shows MBRC for 1 route for 1 Service (Rail) by month

When I want to pull the MBRC for this service in a measure, I need to use an aggregate function (AVERAGE) but because there is only one value for each month, I get either the sum of all MBRC in the column for that route or an Average of all MBRC for the month and not the value of the month

This is the data in TABLE2 that holds the MBRC of Rail. The correct value to show when MetroRail is selected from the slicer is 7831 for the month of February but the aggregate function I need to use to pull this MBRC into the SELECTEDVALUE(Services) measure averages the MBRC column and provides the average of the entire column when all I need is the actual value of the column for the month and service selected.

Regardless of the month I choose for MetroRail, I get the average or sum whichever aggregate I use for that month. For instance This is December 2020 and I get the AVERAGE of the entire MBRC column

[Anonymous]

@MRGMSR , so in summary: when Metrorail is selected in the slicer, the value in the visual Miles Between Roadcalls is incorrectly returning 44.59k when the correct value should be 7.83k. Is that right?

Could you please post the DAX expression for the measure used in the Miles Between Roadcalls visual?

And could you please say which table name and field name is being used to populate the Month slicer at the top-right of the posted images?

[MRGMSR]

Good morning, thanks for responding. Yes, in summary that is exactly what is happening.

The measure used in the Miles Between Road Call card is:

Selected Service MBRC =

VAR SELECTED_SERVICE = SELECTEDVALUE(Services[Service])

Return

SWITCH(SELECTED_SERVICE,

"MetroRail",'1_MeasuresTbl'[Rail MDBF],

"MetroBus", '[EDW]_[VW_CM_RELIABILITY_DASHBOARD]'[MBRC],

"MetroAccess", '[EDW]_[VW_CM_RELIABILITY_DASHBOARD]'[MBRC],

"MetroExpress", '[EDW]_[VW_CM_RELIABILITY_DASHBOARD]'[MBRC],

"MetroRapid", '[EDW]_[VW_CM_RELIABILITY_DASHBOARD]'[MBRC],

"Pickup Service", "None",

"UT Shuttle", "None")

In my desperation, I have used different DAX formulas for the measure used to call Rail MDBF in the Selected Service MBRC measure. None return the value in the table (TABLE2) aka Rail KPI Monthly.  

Rail MDBF =

VAR MMax =MAX('Rail KPI Monthly'[MDBF])

RETURN CALCULATE(AVERAGE('Fiscal Calendar'[Month_Year]),

'Rail KPI Monthly'[MDBF] = Mmax) shows 44.59k regardless of the month chosen

Rail MDBF = AVERAGE('Rail KPI Monthly'[MDBF]: shows 18.10K regardless of the month chosen

Rail MDBF = SUM('Rail KPI Monthly'[MDBF]: 325,259 regardless of the month chosen

The table and field name used to populate the Month slicer at the top-right is

DAX Table named Fiscal Calendar (relate to both tables above by Month_Year to Month and Year)

Field: 'Fiscal Calendar'[Month_Year] this is a concatenated column (month and year)

Month_Year = 'Fiscal Calendar'[Month Abbr] &"-"&'Fiscal Calendar'[Year]

and changed data type to MMM YYYY in Column tools in Power Bi desktop

The data comes from an SQL database and there are many other tables but here is a sample . DATA USED

Thank you so much any help and guidance you can provide will be greatly appreciated!

[Anonymous · Accepted solution]

@MRGMSR , what column in your 'Rail KPI Monthly' table holds the year and month? Is it the [Month] column? What format is that column? What you need to be able to do is have a column in 'Rail KPI Monthly' that can be mapped onto the corresponding month in 'Fiscal Calendar'. Let's assume that in both tables you have a YearMonth column that is in the format YYYYMM.

I notice in the image of your data model that the relatinship between 'Fincal Calendar' and 'Rail KPI Monthly' is dotted, which means it is 'Inactive', so it will not affet how the measures work. The direction of the relationship is also the wrong way - normally you would have a relationship flowing from the Date dimension to the fact table. I assume you have not made the relationship active because there was no correct column to map between them, it that right?

In any case, without changing the relationships, I think your [Rail MDBF] measure needs to be something like this:

Rail MDBF =
   VAR vSelectedYYYYMM = SELECTEDVALUE('Fiscal Calendar'[YYYYMM])
   RETURN
      CALCULATE(
         AVERAGE('Rail KPI Monthly'[MDBF]),
         ALL('Rail KPI Monthly'),
         'Rail KPI Monthly'[YYYYMM] = vSelectedYYYYMM
      )

It would help if you could also post a sample of the contents of the 'Rail KPI Monthly' file.

[MRGMSR]

@Anonymous THANK YOU SO MUCH!!!

The measure worked excellent. I checked the relationship too. I was linking to the wrong column in the Fiscal Year Calendar table from the Rail table. I changed the relationship, added the YYYYMM column, then the measure and it works exaclty as it should.

I apprecaite it so much! You have no idea how much time I spent on this trying to figure out the issue and just ended up frustrated, quitting, then back again.

Thank you! 😄

[Anonymous]

@MRGMSR , you're welcome, glad it helped.

Once the relationship between the 'KPI Rail Monthly' table and the Fiscal Calendar is sorted, then you should be able to change the measure to be just this:

Rail MDBF = AVERAGE('Rail KPI Monthly'[MDBF])

[MRGMSR]

Of course! Yes, the measure now works just using AVERAGE(Rail KPI Monthly [Month])

Honestly, if I could give you an award, I would. You have no idea how much this helped. I have to finish this by tomorrow and I was just about to quit on it, you saved me.

Again, thank you and have an awesome day!

[Anonymous]

@MRGMSR , you're welcome, happy to help. No award needed (but I do like scotch whisky 😉)

This problem you posted is a good demonstration about the importance of getting the data model right. When the model is right, the measures are so much simplier.

Although I understand it can be hard to find out exactly where a problem actually is, especially when there is the presure of a deadline looming. In your case, the clue was when you said that the MetroRail figures were the same regardless of which month was selected. When a measure returns the same value for any slicer value selected, or for every row in a table, it often means the relationship between the relevant tables is not working as intended.

[MRGMSR]

These are the relationships from all tables to calendar and services tables