Ignoring Most Recent Days/Records in DAX Measure (Graph)

DAX DAX Calculations
1

I have an interesting problem. I have a few measures that are similar to below, where we want to assess the success rate of patches being applied to assets within 60 days of discovery of the vulnerability.

`
JavaScript

  __linux_os_goal =

// # of linux assets critical/high with os patches applied within 60 days
var MEASURE_VALUE = CALCULATE(DISTINCTCOUNT(‘public fact_asset_vulnerability_finding_date’[asset_id]),
‘public dim_asset’[os_family] = “Linux”,
OR(‘public dim_vulnerability’[dlx_severity] = “Critical”, ‘public dim_vulnerability’[dlx_severity] = “High”),
‘public fact_asset_vulnerability_finding_date’[days_known] <= 60, ‘public fact_asset_vulnerability_finding_date’[status] = “Closed”,
‘public dim_vulnerability_category’[category_group] = “Linux OS”)

// # of total linux assets with critical/high os vulnerabilities
var BASELINE_VALUE = CALCULATE(DISTINCTCOUNT(‘public fact_asset_vulnerability_finding_date’[asset_id]),
‘public dim_asset’[os_family] = “Linux”,
OR(‘public dim_vulnerability’[dlx_severity] = “Critical”, ‘public dim_vulnerability’[dlx_severity] = “High”),
‘public dim_vulnerability_category’[category_group] = “Linux OS”)

RETURN

DIVIDE(MEASURE_VALUE, BASELINE_VALUE)`

<<<<<<<<<<<<<

I’ll attach the graph which results.
Graph where the final 60 days of measurement should be ignored.docx (119.7 KB)

The issue is with those assets that are still within the 60 day time frame, that results in the end of the graph always falling to 0. Is there a way to remove the most recent 60 days from being measured?

Thank you,
Kevin

2

@kkeigr,

Can you please send me a pbix file with the sample data that you created so I get a better understanding of the issue?

Thanks,
–Joe

3

pbix file is very large… do you have a Cloud location into which for me to drop it?

4

Hi Kevin,

You can use the technique shown here by @BrianJ to mask and strip your data, so you can easily share a good representation of your actual file but with limited size.

I hope this is helpful.

1 Like
5

Melissa & Joe,

I can certainly work on a “reduced and non-proprietary” pbix/dataset, but I was thinking that I’ve seen a video re: how to suppress values in measure or in the graph from which such measures are displayed. In this case, I’m not looking to remove the most recent 60 days of data from the queries/tables via Power Query, but only display measure values in the graph until the data point representing 60 days ago. I’m attaching a little better visual, and am looking for some general recommendations or instruction on how to ignore or suppress the most recent 60 days from the measure and/or it’s line graph representation.
Graph where the final 60 days of measurement should be ignored.docx (133.1 KB)

6

Perhaps this is the video you recall seeing.
Guy in a cube

It requires adding a column to your date dimension table that indicates the relevant period for your measures.

Add a column such as this:
OlderThan60 =
IF( DATEDIFF(‘Calendar’[Date], TODAY(),DAY) >= 60,
1,
0
)

Once you have the column, you can incorporate it into the calculate table pattern from the video.

I hope this helps!

Jamie

2 Likes
7

Thanks to all. Very helpful. I ended up with a filter on my measure VARs which produced the desired results:

__linux_os_goal_2 =

var LastDataDate = CALCULATE( MAX ( ‘public fact_asset_vulnerability_finding_date’[data_written_date] ), ALLEXCEPT(‘public fact_asset_vulnerability_finding_date’, ‘public fact_asset_vulnerability_finding_date’[data_written_date]) )

// # of linux assets critical/high with os patches applied within 60 days
var MEASURE_VALUE = CALCULATE(DISTINCTCOUNT(‘public fact_asset_vulnerability_finding_date’[asset_id]),
‘public dim_asset’[os_family] = “Linux”,
OR(‘public dim_vulnerability’[dlx_severity] = “Critical”, ‘public dim_vulnerability’[dlx_severity] = “High”),
‘public fact_asset_vulnerability_finding_date’[days_known] <= 60, ‘public fact_asset_vulnerability_finding_date’[status] = “Closed”,
‘public dim_vulnerability_category’[category_group] = “Linux OS”,
FILTER( ALL(‘public fact_asset_vulnerability_finding_date’[data_written_date] ),
‘public fact_asset_vulnerability_finding_date’[data_written_date] < LastDataDate-60
) )

// # of total linux assets with critical/high os vulnerabilities
var BASELINE_VALUE = CALCULATE(DISTINCTCOUNT(‘public fact_asset_vulnerability_finding_date’[asset_id]),
‘public dim_asset’[os_family] = “Linux”,
OR(‘public dim_vulnerability’[dlx_severity] = “Critical”, ‘public dim_vulnerability’[dlx_severity] = “High”),
‘public dim_vulnerability_category’[category_group] = “Linux OS”,
FILTER( ALL(‘public fact_asset_vulnerability_finding_date’[data_written_date] ),
‘public fact_asset_vulnerability_finding_date’[data_written_date] < LastDataDate-60
) )

RETURN

DIVIDE(MEASURE_VALUE, BASELINE_VALUE)