Forum Discussion
Filtering down to one record with DAX
Hello,
I'm trying to get one value in a measure and to do this I need to filter a table by four different columns.
I can't use a calculated column for this because it takes too much calculation time.
The goal is to create a chart where the numerator can be chosen from a list or a slicer.
The denominator should be a different value that should not be filtered otherwise it would be zero.
My question is: Is it possible to get a specific value from a table just by using a measure and not a slicer on the visualisation screen?
(I have to set four columns to a value to get down to one record in the table)
The filter()-function can only take two arguments and thus only filter once.
When I try to nest filters like that
You may also modify the measure like below:
gl = CALCULATE ( LASTDATE ( 'Facts'[PeriodID] ); FILTER ( 'Facts'; 'Facts'[column1] = "value1" && 'Facts'[column2] = "valuex" && 'Facts'[column3] = "valuey" ) )If you still have other issues, please share some sample data and give the expected result.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi PBIUser1316 ,
gl = filter(filter(filter(filter('Facts';'Facts'[column1]="value1"); 'Facts'[column2]="valuex"); 'Facts'[column3] = valuey); LASTDATE('Facts'[PeriodID])) -------------------------------- gl = VAR column1value = "value1" VAR column2value = "valuex" VAR column3value = "valuey" RETURN CALCULATE ( LASTDATE ( 'Facts'[PeriodID] ), 'Facts', 'Facts'[column1] = column1value, 'Facts', 'Facts'[column2] = column2value, 'Facts', 'Facts'[column3] = column3value )
This is just an example of what you might be looking for in your code. Unless you post more information, we won't be able to help you more than this. You will need to look at CALCULATE() and Variables.
Please read this post to get your question answered more quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielYou may also modify the measure like below:
gl = CALCULATE ( LASTDATE ( 'Facts'[PeriodID] ); FILTER ( 'Facts'; 'Facts'[column1] = "value1" && 'Facts'[column2] = "valuex" && 'Facts'[column3] = "valuey" ) )If you still have other issues, please share some sample data and give the expected result.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks that really helped me to apply multiple filters with the "&&".
One last problem that I have is, that I need one measure not to be affected by a slicer.
I tried the all-Function like this:
glistunfilterable = all('Dax Measures'; [glist])=> error: "the ALL function expects a table reference expression for argument '2', but a string or numeric expression was used."And like this:glistunfilterable = all('Dax Measures'; 'Dax Measures'[glist])With the same error message.So it looks like I can't use the all-function with a measure.Can I use the all-function with a filter-function somehow?
