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 connec...
  • 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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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's avatar
    Anonymous
    Not applicable
    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.

     

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

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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])