Keeping original selected Begin & End Dates via DAX (ignore bar chart selection)

DAX DAX Calculations
1

Hi,
I need a way to ignore bar chart selection by keeping the original total via DAX query. (not through visual interactions.)

See below pic:
Where I have a ‘KPI total patients’ & a bar chart represents each day #. Simple.

image
image.png1191×388 9.66 KB

Now, I need a way to keep this ‘Grand Total’ for the selected period with DAX Query: - image

by ignoring any selection on bar chart. desired grant total = 1097. How do I achieve that ? Could someone please guide me?

I tried the function ALLSELECTED(). But is not working. ALLSELECTED() contains 1 day when a bar chart is clicked. – see measure: Total Patients (fixed).

image
image.png978×386 13.2 KB

here is the pbix file. PAS-New- AccessNumber attempt - added open orders compute.pbix (1.1 MB)

Thanks!

2

ALLSELECTED won’t work here because it recognizes that you have selected another date from the date table.

Basically you just need to use visual interactions if you specifically want this. Why can’t you use it? This seems to make a lot of sense if it’s what you are after.

Here’s another option for you that can work but it requires creating another date table.

This sort of technique is more advanced though. It’s a bit different to exactly you one but it will give you the idea around how to solve this. This more just shows you how to create date insights that don’t interact with each other.

I honestly do believe though visual interactions is what this is perfect for.

See how you go

Thanks
Sam

4

Hi @akiko_lim

As SAM mentioned “Edit Interactions” is best way to handle it. However to handle without Interaction, you can try below.

In Bar Chart use Date Field from Fact Table (CollectDetails) instead of Date Table . Then create a new measure for display in Card as below.

Total Patients (fixed) =
CALCULATE (
[Total Patients],
FILTER ( ALL ( CollectDetails[coll_date_only] ),
CollectDetails[coll_date_only] IN VALUES ( ‘Calendar’[theDate] ) ))
OR
Total Patients (fixed) =
CALCULATE([Total Patients],TREATAS(VALUES(‘Calendar’[theDate]),CollectDetails[coll_date_only]))

Capture

Also, attached the PBIX file.
ES_Forum - PAS-New- AccessNumber attempt - added open orders compute.pbix (1.1 MB)

Thanks
Ankit

2 Likes
5

Thanks Sam & Ankit. Your explains make sense.
Your solutions/suggestions are very helpful. Appreciate Sam pointing me to the training video! You guys are awesome!

6

@ankit,

These are both really elegant solutions. I was working on this one using a second disconnected date table when I saw your post. Use of TREATAS to handle this without complicating the data model didn’t occur to me.

Good stuff. :+1:

  • Brian
7

Thanks @BrianJ for appreciation. I also tried few things before coming with this solution.

Just trying to get used to kind of questions we receive on this forum and how detail explanation you guys provide. Hopefully will be able to contribute more in future.

Thanks,
Ankit Jain

8

@ankit,

I think your forum posts have been terrific. One suggestion though is to format any DAX code you provide to make it more readable and easier to understand. Here’s a brief video on how to do that within the forum:

https://forum.enterprisedna.co/t/how-place-dax-formula-into-forum-topics-posts/156

Thanks. Look forward to seeing more of your posts in the future.

  • Brian