Forum Discussion
Helper VHow to Add a Filter to a Measure Using a Column From A Related Table
I have a simple model:
I have this measure in the HX table:
=CALCULATE(
Min(HX[HXDate]),HX[NewValue] = "Approved")I need to expand on this measure by filtering the HX table to only those rows where HX[CreatedById]=Users[Id].
In other words:
Give me the Min HX Date from the HX table WHERE HX[NewValue] = "Approved" and WHERE HX[CreatedById]=Users[Id].
I have spent weeks googling, reading, watching videos and I can't find a solution.
If you can help, I would dearly appreciate it.
First the two tables and the expected result
Connection on the ID field.
Expected result:
"a" returning "1/2/2025".
"b" should return nothing because it has no "Approved" rows.
"c" should return nothing because it does not exist in the Users table, even though it does have an "approved" row.DAX measure:
mindate = CALCULATE(MIN([HXDate]), HX[NewValue] = "Approved")
Add a filter to exclude blank User[Id] rows.
And the result exactly as we expected it to be:See attached .pbix with the solution.
///Mediocre Power BI Advice, but it's free///
3 Replies
kpostSolution Sage
First the two tables and the expected result
Connection on the ID field.
Expected result:
"a" returning "1/2/2025".
"b" should return nothing because it has no "Approved" rows.
"c" should return nothing because it does not exist in the Users table, even though it does have an "approved" row.DAX measure:
mindate = CALCULATE(MIN([HXDate]), HX[NewValue] = "Approved")
Add a filter to exclude blank User[Id] rows.
And the result exactly as we expected it to be:See attached .pbix with the solution.
///Mediocre Power BI Advice, but it's free///
TxtcherThank you.
Yikes, I feel ignorant. But...
As a beginner trying to understand "context" I find that a lot of the tutorials jump right into the more complex theories and neglect to start at the very, very beginning. In fact, I was so deep into the forest, I couldn't see the trees. It is taken for granted a user understands how the relationships affect each other by adding them to a visual. I know it is a pretty basic concept to an experienced user, but to someone brand new, it should not be taken for granted.
I am just now starting to understand how the relationshps can act as a filter if you add them to a visual.
I do have one question: Why do they all contain blank selection? Is that so you can override them as a filter? That's something else that is never mentioned or demonstrated in beginner tutorials.
Thank you again for going to all the trouble. I dearly appreciate it.
Ashish_ExcelSolution Supplier
Hi,
Just create a slicer of ID from the Users table and select a certain ID there. Your measure should work.