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.

     

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

 

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

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

    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.