Forum Discussion
Helper IHelp with Multiple Table Measures with and without Aggregate
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.
Solution SageHi 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.
Helper IHopefully, 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
- MRGMSR
These are the relationships from all tables to calendar and services tables
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 chosenRail MDBF = AVERAGE('Rail KPI Monthly'[MDBF]: shows 18.10K regardless of the month chosenRail MDBF = SUM('Rail KPI Monthly'[MDBF]: 325,259 regardless of the month chosenThe table and field name used to populate the Month slicer at the top-right isDAX 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 desktopThe data comes from an SQL database and there are many other tables but here is a sample . DATA USEDThank you so much any help and guidance you can provide will be greatly appreciated!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.