Forum Discussion

MRGMSR's avatar
MRGMSR
Icon for Helper I rankHelper I
4 years ago
Solved

Help with Multiple Table Measures with and without Aggregate

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_NAMEMONTH_END_DATEMONTH_NAMEMONTH_BEGIN_DATEMONTH_YEAR_NAMECALENDAR_MONTH_NUMBERCALENDAR_YEAR_NUMBERROUTE_SORT_ORDERRELIABILITY_GOALFISCAL_YEAR_NUMBERFISCAL_MONTH_NUMBERMODE_NAMEROUTE_NAMEINCIDENT_NAMEINCIDENTSTOTAL_INCIDENTSOTP_PCTYTD_OTP_PCTQTD_OTP_PCTMEAN_MILES_BETWEEN_ROAD_CALLSDATA_AS_OFOTP_Month_YearDateKey1_
Aug########AugustSunday, August 1, 202120-Aug8202128300.00%202111MetroBus1-North Lamar/South CongressEARLY154126395583.00%4.635.713415.783########Aug-212021080116042
Aug########AugustSunday, August 1, 202120-Aug8202198300.00%202111MetroBus10-South 1st/Red RiverEARLY141323582599.00%4.565.893415.783########Aug-212021080116042
Aug########AugustSunday, August 1, 202120-Aug82021228300.00%202111MetroBus103-Manchaca FlyerEARLY28321872.00%7.627.373415.783########Aug-212021080116042
Aug########AugustSunday, August 1, 202120-Aug82021258300.00%202111MetroBus111-South Mopac FlyerEARLY12326368.00%4.433.273415.783########Aug-212021080116042
Aug########AugustSunday, August 1, 202120-Aug82021238300.00%202111MetroBus105-South 5th FlyerEARLY13330393.00%1.842.543415.783########Aug-212021080116042
Aug########AugustSunday, August 1, 202120-Aug82021288300.00%202111MetroBus135-Dell LimitedEARLY5164304.00%2.532.993415.783########Aug-212021080116042

 

 TABLE2

MonthMDBFRail Month_YearDateKeyRoute_IDService

December 202014191December 202020201220550MetroRail
January 202125379January 202120210121550MetroRail
February 20219976February 202120210221550MetroRail
March 202110198March 202120210321550MetroRail
April 202129263April 202120210421550MetroRail
May 202114179May 202120210521550MetroRail
June 20215930June 202120210621550MetroRail
July 202116500July 202120210721550MetroRail
August 202116042August 202120210821550MetroRail
September 202138583September 202120210921550MetroRail
October 202119580October 202120211021550MetroRail
November 202134612November 202120211121550MetroRail
December 202137431December 202120211221550MetroRail
January 20228655January 202220220121550MetroRail
February 20227831February 202220220221550MetroRail
March 20228811March 202220220321550MetroRail
April 20229729April 202220220421550MetroRail
May 202218862May 202220220501550MetroRail

 

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.

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

11 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      MRGMSR
      Icon for Helper I rankHelper I
      4 years ago

      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

       

      • MRGMSR's avatar
        MRGMSR
        Icon for Helper I rankHelper I
        4 years ago

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

         

  • MRGMSR's avatar
    MRGMSR
    Icon for Helper I rankHelper I
    4 years ago

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