Filtered List

[Dayna]

Hi All,

It's been a while since I've used PowerBI and struggling to remember how to use it! 😉 

I'm trying to create two different measures:

1) Show all items (servername) in the table "Servers" where no record exists in "TestingLog"

2) Show all items (servername) in the table "Servers" where no record exists in "TestingLog" for the last 12 months (field is is 'Test Date'

I tried to do a count with the following formula, but I don't think it's quite right. If you could help me with the two lists above, it would be really appreciated.

CountSystemsNotTested =
CALCULATE (
DISTINCTCOUNT ( 'DR Server'[ServerName] ),
FILTER ( 'DR TestingLog', 'DR TestingLog'[TestDate] = BLANK () ), FILTER(ALLSELECTED('DR Server'), 'DR Server'[ServerStatus] = "LIVE")
)

Many thanks,
Dayna

[ibarrau]

Hi. I think it should be easier. Try this out:

CALCULATE(DISTINCTCOUNT(Server[Id])
; ISBLANK(TestingLog[Server_id])
)

Consider column Id from Server the one related with column Server_id in TestingLog. Using the related columns it should be like that.

For the last 12 months is going to be quite different. You can add a filter in calculate like:

CALCULATE ( [....]
, 'TestingLog'[TestDate] >= Date(YEAR(EDATE(today();-12));MONTH(EDATE(today();-12));1)
)

Hope something helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dayna]

Hello,

It's certainly closer than I got before, but I don't want a count of servers, I actually want a list of the servers that match this filter (i.e. where the test log is not blank). Is it possible to return a string list in the calculate method?

Many thanks,

Dayna

[Dayna]

Hello,

I've sorted the count of records without a test performed, but still struggling with the tests performed in the last 12 months. 

I've tried the below:

Servers Not Tested in 12 Months = CALCULATE (distinctcount('DR Server'[Id]), 
    'DR TestingLog'[TestDate] >= Date(YEAR(EDATE(today(),-12)),MONTH(EDATE(today(),-12)),1)
)

But it doesn't seem to be working. It's bringing back 1 for all records, regardless if there's a test date, or when the test date was. 

I'd expect a 1 for records that either don't have a test date, or 1 where the date was outside of the last 12 months.

Any suggestion, please?

Many thanks,

Dayna

[ibarrau]

Ok, I'm not getting the idea when you say "but I don't want a count of servers... ". If you just want the name add name to a visualization and play with visual level filters.

About last 12 months, are you sure 'DR TestingLog'[TestDate] is type Date? Be sure to check the relationship between the tables, if you run a measure with Date(YEAR(EDATE(today(),-12)),MONTH(EDATE(today(),-12)),1) and add it to a visualization it will return the first day of the last 12 months. That is the value to compare the count.

Regards,

[Dayna]

I've added that as a measure and added it to a table and yes, it brings back 01/10/2018 for all entries. 

The TestDate in the TestingLog should be a date - that's what it is set as under the modelling tab - format. It's a direct relationship and very simple, so I can't see an issue, it just brings back 1 for everything regardless of the date / blank value. Does the formula need to be extended to take into account blank records?

I have a small measure to get the last test date:

Last Test Date = lastdate('DR TestingLog'[TestDate])

I've created another measure for the date last year:

Last Year Date = Date(YEAR(EDATE(today(),-12)),MONTH(EDATE(today(),-12)),1)

Is it possible to do something like this?

Servers Not Tested in 12 Months = CALCULATE (distinctcount('DR Server'[Id]), 
    [Last Test Date] >= [Last Year Date]
)

Currently this breaks the visualisation, I assume calculate doesn't work with measures!

Many thanks for your assistance,

Dayna