How to calculate number of values which is not exist in table

[Vitaliy]

Hi !

Please, help with this task:

I have 2 simple tables: Issue table and Metric table. In result mut be result table and bar chart like in screenshot.

Description:
1) Issue is SLA if fact time - plan time <= 0.
1.1) Also, issue can be without some metrics. In this case we thought that this is also SLA issue.
2) Otherwise - issue is Non-SLA.
How can I calculate number of issues in 1.1 and total number of SLA issues ?(

[v-yuaj-msft · Accepted solution]

Hi @Vitaliy ,

Based on your description, you can create some measures as follows.

SLA Issue = calculate(count(Issue[Issue]), filter(Issue, (Issue[Face Time,h] - Issue[Plan Time,h]) <=0 ))+CALCULATE(DISTINCTCOUNT(Issue[Metric]))

SLA Non Issue =

var x1=calculate(count(Issue[Issue]), filter(Issue, (Issue[Face Time,h]- Issue[Plan Time,h]) >0))

return

IF(

    x1=BLANK(),0,x1)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

[amitchandak]

@Vitaliy , Assume issue and Matrix table has many to one relationship, Try measures like

SLA Issue = calculate(count(Issue[Isssue]), filter(Issue, (Issue[fact time] - Issue[plan time]) <0 ))
SLA Non Issue = calculate(count(Issue[Isssue]), filter(Issue, (Issue[fact time] - Issue[plan time]) >=0 ))

[Vitaliy]

@amitchandak 
This is not working as expected(
At first, I edited measures like this:
SLA Issue = calculate(count(Issue[Isssue]), filter(Issue, (Issue[fact time] - Issue[plan time]) <=0 ))
SLA Non Issue = calculate(count(Issue[Isssue]), filter(Issue, (Issue[fact time] - Issue[plan time]) >0 ))
And main thing that if I add this measures to bar chart or table I will not see result that I would expect. In result must be shown for every Issue and every metric is SLA issue in metric or not