Measures with Date Tables

[Anonymous]

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

[mussaenda]

@parry2k's answer is way better!

[Anonymous]

Thanks for your help @mussaenda  🙂

[parry2k · Accepted solution]

@Anonymous sorry missed a bracket

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

[Anonymous]

It works perfectly @parry2k 

Thanks very much for the help.  Much appreciated! 🙂

[parry2k]

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

[Anonymous]

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]

try setting your relationship direction to both

[parry2k]

@Anonymous problem is your measure is returning 1 when there is no value in divided by and that's why you are seeing all the dates. I wouldn't recomment to set the relationship to both direction since it can have performances issue on large dataset.

try following

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

[Anonymous]

Hey @parry2k ,

I tried what you said but got the following error message:

The syntax for 'RETURN' is incorrect. (DAX(VAR d = DIVIDE (SUM ( Table1[Incident Breached] ),SUM ( Table1[Count])RETURNIF (ISBLANK(d), BLANK(),1-d))).

[Anonymous]

Thanks @mussaenda

I changed the direction to 'both' but it still doesn't work ??