topic Re: DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days in DAX Commands and Tips

DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days

cuseman03 — Fri, 08 Nov 2024 21:06:07 GMT

Hi all...so I have a chart, let's call it chart A. that chart is filtered on today's date because it's used as a monitoring chart for infrastructure incidents. I have chart B which is supposed to show past 7 days. Ideally, when something shows up in chart A that's bad, as it means it's a problem ONLY today. I have a filter set to incident shows up in table if remaining disk space is <20%. So let's say Server A shows up in Chart A. Below this, in Chart B, I want to see that server's prior 7 day disk usage. It does not work when I have Chart B set to relative date. In every case, when I click on a server name in Chart A, all it shows me in Chart B is today's data.

I want to be able to see results (filtered on today's date) from Chart A and then drill in for values in Chart A to today's date -7 days for Chart B. Literally everything I have tried ends up in Chart B as only showing data for today's date.

the other issue is I have a parent child hirearchy in 'Server', which has many child 'Disk Drives'. Violations that show up in Chart A are based on the storage space for 'Disk Drives', on today's date (Chart A). I want to be able to click on the Hostname field, and have ALL drives for that server show me today -7 in Chart B (the drilled in chart).

Chart A (based on Nov 8th)

Chart B ( Based on Nov 1-7th. these are separate values, used for illustrative not supposed to match)

So Server 'A' has 5 disk drives. Today, one of them went to 0% free. Only for today was drive V in violation. I want to have Chart B show me 7 day history for ALL of server A's 5 drives.

Thanks

Re: DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days

VahidDM — Sat, 09 Nov 2024 01:28:29 GMT

Hi cuseman03

Issue:

When you select a server in Chart A (filtered to show today's data), Chart B only displays data for today instead of showing the last 7 days for that server. You want Chart B to display the 7-day history of all disk drives for the selected server, regardless of the date filter applied in Chart A.

Solution:

To achieve this, modify the DAX measure used in Chart B to:

Ignore the date filter coming from Chart A.
Apply a new date filter to show the last 7 days.
Keep the server filter from the selection in Chart A.

Steps:

Create a New Measure for Chart B:

DiskUsage_Last7Days =

CALCULATE(

[Disk Usage Measure],

REMOVEFILTERS('Date'),

DATESINPERIOD(

'Date'[Date],

TODAY(),

-6,

DAY

)

Explanation:

CALCULATE: Modifies the filter context for the measure.
[Disk Usage Measure]: Your existing measure calculating disk usage.
REMOVEFILTERS('Date'): Removes any date filters, including the one from Chart A.
DATESINPERIOD(...): Applies a filter for the last 7 days starting from today.
The server filter from Chart A remains active, so the measure only calculates for the selected server.

Replace the Measure in Chart B:

Use DiskUsage_Last7Days as the value in Chart B.
This ensures Chart B always shows the last 7 days for the selected server.

Verify Visual Interactions:

Ensure that Chart A filters Chart B on the Server field.
The date filter should not interfere due to the REMOVEFILTERS('Date') in the measure.

Alternate Measure (Based on Data's Latest Date):

If your data's latest date isn't today, adjust the measure to use the maximum date in your data:

DiskUsage_Last7Days =

VAR MaxDate = MAXX(ALL('Date'), 'Date'[Date])

RETURN

CALCULATE(

[Disk Usage Measure],

REMOVEFILTERS('Date'),

DATESINPERIOD(

'Date'[Date],

MaxDate,

-6,

DAY

)

Notes:

Keep Filters on Server and Disk Drives:

The measure retains filters on Server and Disk Drives from Chart A.
Only the date filter is overridden.

Multiple Selections:

If multiple servers might be selected, consider using VALUES('Table'[Server]) and adjust the measure accordingly.

Ensure Proper Relationships:

Verify that your data model has correct relationships between the tables involved.

Conclusion:

By adjusting the DAX measure to ignore the date filter from Chart A and applying a new filter for the last 7 days, Chart B will display the historical disk usage for all drives of the selected server. This approach allows you to monitor current issues and investigate historical trends seamlessly.

Example of Final Measure:

DiskUsage_Last7Days =

CALCULATE(

[Disk Usage Measure],

REMOVEFILTERS('Date'),

DATESINPERIOD(

'Date'[Date],

TODAY(),

-6,

DAY

)

Use this measure in Chart B to achieve the desired functionality.

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days

cuseman03 — Mon, 11 Nov 2024 18:51:38 GMT

this makes sense in theory but I'm struggling to get it to look right. To be clear, I want Chart B not to sum the usage over 7 days but to show the usage across 7 days with the X axis being the last 7 days.

my original DAX for getting percent free is

DIVIDE(

SUM('StorageSpaceUtilization'[Free(GB)]),

SUM('StorageSpaceUtilization'[Size(GB)])

)

I then used this measure in your above recommendation

CALCULATE([FreeSpace], REMOVEFILTERS(StorageSpaceUtilization[FileDate]), DATESINPERIOD(StorageSpaceUtilization[FileDate].[Date], TODAY(),-6,DAY))

However the values look incorrect. . For Chart B, I should have 7 values for any given server I click on. That's the past 7 days. I can't get Chart A to disassociate and still show chart B correctly. I want to have a line graph on Chart B of the below data

Also, how do i get Chart B to only display the drive that is affected. This is tricky because it could be one to many. For example, if in Chart A Server 4845 has 2 drives that have <20%, when I click on that I only want those two to show up on the below chart.

Re: DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days

Anonymous — Tue, 12 Nov 2024 09:26:06 GMT

Hi cuseman03 ,

Can you provide the sample data? What is the FileDate column? What’s more, are there two tables or just one table of data?

Best Regards,

Wisdom Wu

Re: DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days

cuseman03 — Tue, 12 Nov 2024 14:50:22 GMT

I'm unable to get this. I'm thinking it is because I do not have a separate date table. I am not sure.