Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
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!
Solved! Go to Solution.
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
The slicer is based on Date1:
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.
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
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
The slicer is based on Date1:
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.
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |