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.
(Once again, a very basic question. Sorry, I have just started studying Power BI)
I have a table that contains several combinations of the columns Name (string) and Date (date). I would like to be able to filter both Name and Dates using slicers and be able to find the min/first date of each Name in the sliced result.
Dataset:
Name Date
A | 27/12/2018 |
A | 28/12/2018 |
A | 29/12/2018 |
A | 30/12/2018 |
A | 31/12/2018 |
B | 29/12/2018 |
B | 30/12/2018 |
B | 31/12/2018 |
I was able to calculate the min/first Date with Name sliced using the following column:
Actual Output A = CALCULATE(FIRSTDATE(Table[Date]); ALLEXCEPT(Table; Table[Name]))
But, if I try to filter the Date using a slicer, it doesn't work. Therefore, I also tried something like:
Actual Output B = CALCULATE(FIRSTDATE(Table[Date]); ALLEXCEPT(Table; Table[Name]; Table[Date]))
But the code above doesn't work, as it feels that once Table[Date] is included in the filter, it will return the same Date it is querying. Here is the expected output and expected output for my scenario (having the slicer filter Dates greater or equal than 28/12/2018):
Name Date Expected Output Actual Output A Actual Output B
A | 28/12/2018 | 28/12/2018 | 27/12/2018 | 28/12/2018 |
A | 29/12/2018 | 28/12/2018 | 27/12/2018 | 29/12/2018 |
A | 30/12/2018 | 28/12/2018 | 27/12/2018 | 30/12/2018 |
A | 31/12/2018 | 28/12/2018 | 27/12/2018 | 31/12/2018 |
B | 29/12/2018 | 29/12/2018 | 29/12/2018 | 29/12/2018 |
B | 30/12/2018 | 29/12/2018 | 29/12/2018 | 30/12/2018 |
B | 31/12/2018 | 29/12/2018 | 29/12/2018 | 31/12/2018 |
Could somebody help me out? Thanks in advance.
Solved! Go to Solution.
I'm assuming you've filtered out the Name:A Date:27/12/2018 entry by a date slicer?
You'll get correct output with this:
Actual Output C = CALCULATE(FIRSTDATE('Table'[Date]); ALLSELECTED('Table'[Date]))
You were very close with A, but ALLEXCEPT completely removes all filters except the ones on columns you specify. By calling out 'Table'[Name], all previous filters on 'Table'[Date] were removed, including the one applied by your slicer.
ALLSELECTED returns all data that currently shows up in the visual the measure is used in. So it can be used to remove filters applied by the current row of a table, but still respect filters from slicers or global report filters.
In your case, you want to keep filters that have been applied to [Name], since getting the minimum date shown in the entire table would be wrong for B. So by explicitly using ALLSELECTED('Table'[Date]), you return all values available in the visual, but only remove filters applied by the row context of the table on [Date].
I'm assuming you've filtered out the Name:A Date:27/12/2018 entry by a date slicer?
You'll get correct output with this:
Actual Output C = CALCULATE(FIRSTDATE('Table'[Date]); ALLSELECTED('Table'[Date]))
You were very close with A, but ALLEXCEPT completely removes all filters except the ones on columns you specify. By calling out 'Table'[Name], all previous filters on 'Table'[Date] were removed, including the one applied by your slicer.
ALLSELECTED returns all data that currently shows up in the visual the measure is used in. So it can be used to remove filters applied by the current row of a table, but still respect filters from slicers or global report filters.
In your case, you want to keep filters that have been applied to [Name], since getting the minimum date shown in the entire table would be wrong for B. So by explicitly using ALLSELECTED('Table'[Date]), you return all values available in the visual, but only remove filters applied by the row context of the table on [Date].
Hi - I would really appreciate your help on this. I am having what I think is the same problem but your solution is not working for me.
The formula below gives me the correct First Date but when I try to slice using my date table all my First Date results are filtered to the same date as chosed in the slicer.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |