Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measures with Date Tables

Hi,

 

I am very new to Power BI and I am having trouble using a measure with a date table.

 

I created a date table for looking up

Dates = CALENDAR(TODAY()-1000,TODAY()+1000)

 

within which I made a column called MonthYear

MonthYear = 'Dates'[MonthShortName]&" "&'Dates'[Year]

 

When I create charts from my 'Fact Sheet' (Table1) it works fine and groups the data in MonthYear as I want it to do.

 

However, when I make a chart that has this measure in it

(

SLA % = 1-
DIVIDE (
SUM ( Table1[Incident Breached] ),
SUM ( Table1[Count])
)
 
It calculates the SLA % correctly for the months within my 'Fact Sheet' (Table1) but also tries to calculate SLA % for every month in the 'Date table' where there are no corresponding months in my 'Fact Sheet' (Table1).
 
 
I hope this makes sense.
 
Any help would be much appreciated.
 
Thanks,
Martin
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    Anonymous sorry missed a bracket

     

    SLA % = 
VAR d = 
DIVIDE (
  SUM ( Table1[Incident Breached] ),
  SUM ( Table1[Count])
)
RETURN
IF (ISBLANK(d), BLANK(),1-d)

10 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous did you setup relationship between your date and transaction table?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Wow, that was quick :-)  Thanks for getting back to me parry2k 

       

      Yes, I think(?) I have

      Here you can see two charts, both are using the same MonthYear axis 

      But the one in the left (which does not use a measure) seems to work fine.

      • mussaenda's avatar
        mussaenda
        Icon for Community Champion rankCommunity Champion
        7 years ago

        try setting your relationship direction to both

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      7 years ago

      Anonymous sorry missed a bracket

       

      SLA % = 
VAR d = 
DIVIDE (
  SUM ( Table1[Incident Breached] ),
  SUM ( Table1[Count])
)
RETURN
IF (ISBLANK(d), BLANK(),1-d)
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        It works perfectly parry2k 

         

        Thanks very much for the help.  Much appreciated! :-)