cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter a table visual based on the latest data update

Hello guys!

 

I have a dashboard which it's data is based on a position for open items, so every day I have a programmed routine that downloads the data into my dashboard and it get's updated. Considering the data is for open items, one specific item can appear multiple times until it's cleared, so I've been working only with the latest date and keep the past information as history. Here's a glimpse of the dashboard:

 

dash covid 1.png

 

On the top right, I created a measure that gets the latest date on the data and all the other measures also use that information to display it's info (based on the context). I also created a line chart so people can see the data history and to be able to select a specific date in time, so when I interact with a data point on the line chart all the data get's updated to that specific position/context in time. Everything works fine, except for one particular visual. As you guys can see, there is a table visual; the highlighted column on the table specifies what database the user is seeing the information and it's not having the same behaviour as the other visuals on the dashboard, because it's displaying all the data when it should only bring data from 2020-03-27; I have a few columns on that table that are actually measures, the same as the ones being used on the other visuals, that get's the maximum date from the context, but they are not filtering the data on the table.

 

dash covid 2.png

 

When I select a specific date on the line chart, as obviously expected, the table gets filtered alright, as the other visuals, but what I need for it to do is, when nothing is selected, to display only the data from the latest date available.

 

Any thoughts on the matter? I'm pretty sure I can solve this with DAX, but everything I tried works with every single visual available, except for tables.

 

Thank you very much!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: Filter a table visual based on the latest data update

Hi @marlonip ,

 

I assume that you do not use a separate table to select the dates (otherwise you need a slightly different solution).

 

This is the sample tabel I used

2020-03-29 10_23_43-Window.png

 

The slicer is based on Date1:

2020-03-29 10_35_33-Window.png

As you can see, when there is no selection the latest date rows are shown.

 

When you make a selection, it still only shows the selection.

 

2020-03-29 10_37_10-Window.png

 

These are the measures I use:

Max Date1 := max(Table1[Date1]) -- 'calculate' row level maxdate in measure

Selected Date :=
var maxDate = Maxx(all(Table1[Date1]),'Table1'[Date1]) -- store current value for comparison
var selectedDATES = COUNTROWS(ALLSELECTED(Table1[Date1]))
var allDATES = COUNTROWS(ALL(Table1[Date1]))
return
if ( AND(selectedDATES = allDATES, '_keyMeasures'[Max Date1] = maxDate),"Nothing Selected",IF(selectedDATES<allDATES,"Selection"))

 

I hope you can transfer this to your own dashboard.

 

Jan 

 

View solution in original post

2 REPLIES 2
Highlighted
Resolver IV
Resolver IV

Re: Filter a table visual based on the latest data update

Hi @marlonip ,

 

I assume that you do not use a separate table to select the dates (otherwise you need a slightly different solution).

 

This is the sample tabel I used

2020-03-29 10_23_43-Window.png

 

The slicer is based on Date1:

2020-03-29 10_35_33-Window.png

As you can see, when there is no selection the latest date rows are shown.

 

When you make a selection, it still only shows the selection.

 

2020-03-29 10_37_10-Window.png

 

These are the measures I use:

Max Date1 := max(Table1[Date1]) -- 'calculate' row level maxdate in measure

Selected Date :=
var maxDate = Maxx(all(Table1[Date1]),'Table1'[Date1]) -- store current value for comparison
var selectedDATES = COUNTROWS(ALLSELECTED(Table1[Date1]))
var allDATES = COUNTROWS(ALL(Table1[Date1]))
return
if ( AND(selectedDATES = allDATES, '_keyMeasures'[Max Date1] = maxDate),"Nothing Selected",IF(selectedDATES<allDATES,"Selection"))

 

I hope you can transfer this to your own dashboard.

 

Jan 

 

View solution in original post

Highlighted
Frequent Visitor

Re: Filter a table visual based on the latest data update

Hi @JustJan, I'm sorry for taking so long to reply!

 

Thank you very much for your help! I was able to, with a few adjustments to my model and measures, to apply your solution to my dashboard. Now everything works fine.

 

Once again, thanks!

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors