Forum Discussion
How can I use a calculated table to filter values in another table?
I have an unpivoted table of survey responses and the columns include:
- Respondent (ID)
- QuestionRoot(text)
- RowIndex(the response options)
- Value(their response)
I have used CalculateTable and Intersect to count the number respondents to particular question/response pairs.
For example, here I count the number of respondents who are from the US and have a certain range of number of employees:
MyMeasure =
VAR Country = CALCULATETABLE(
DISTINCT('Raw Data'[Respondent]),
FILTER('Raw Data',
('Raw Data'[QuestionRoot]="HQCountry") && ('Raw Data'[Value]=242)
)
)
VAR Employees = CALCULATETABLE(
DISTINCT('Raw Data'[Respondent]),
FILTER('Raw Data',
('Raw Data'[QuestionRoot]="NumEmps") && (('Raw Data'[Value] = 2) || ('Raw Data'[Value] = 3) || ('Raw Data'[Value] = 4))
)
)
VAR Target = CALCULATETABLE(
INTERSECT(Country, Employees)
)
RETURN
Countrows(Target)
This works fine to count the records, however now I'd like to do some calculations on the data table using the selected respondents (VAR Target) as a filter. However this method doesn't work:
VAR MedianBudget = CALCULATE(
MEDIAN('Raw Data'[Value]),
FILTER('Raw Data',
('Raw Data'[Respondent] = Target) &&
('Raw Data'[QuestionRoot] = "AnnualBudget") &&
('Raw Data'[ColIndex] = 2)
)
)
RETURN
MedianBudget
The line in the filter where it says "('Raw Data'[Respondent] = Target)" is where it fails, because I'm providing a table where it expects a single value.
Is there a way I can use this calculated table as a filter on the original data table?
Thanks for any suggestions.
Think I figured it out. As I said this failed because I was using a calculated table when DAX expected a single value:
('Raw Data'[Respondent] = (Target))But I found the "IN" command which produces the expected number of records:
('Raw Data'[Respondent] IN (Target))Very slick - lets me use CalculateTable and Intersect to build a list of matching respondents (with as many criteria as I like), then use the resulting table as a filter for other queries on the data.
3 Replies
- Syndicate_Admin
Administrator
Dear you tried to transform the value of Target to number Value(Target), the problem is that the CountRows counts the rows of a table, then it will always bring you a table as the value.
I stay tuned.
Best regards
- sfmike99
Advocate II
The Countrows was just an example. I'm trying to replace Countrows with a Median calculation on the source table. Here's the full code:
MyMeasure = VAR Country = CALCULATETABLE( DISTINCT('Raw Data'[Respondent]), FILTER('Raw Data', ('Raw Data'[QuestionRoot]="HQCountry") && ('Raw Data'[Value]=242) ) ) VAR Employees = CALCULATETABLE( DISTINCT('Raw Data'[Respondent]), FILTER('Raw Data', ('Raw Data'[QuestionRoot]="NumEmps") && (('Raw Data'[Value] = 2) || ('Raw Data'[Value] = 3) || ('Raw Data'[Value] = 4)) ) ) VAR Target = CALCULATETABLE( INTERSECT(Country, Employees) ) VAR MedianBudget = CALCULATE( MEDIAN('Raw Data'[Value]), FILTER('Raw Data', ('Raw Data'[Respondent] = Target) && ('Raw Data'[QuestionRoot] = "AnnualBudget") && ('Raw Data'[ColIndex] = 2) ) ) RETURN MedianBudgetSo the VAR Target is a table containing the Respondents I'm interested in. I want to use that table to filter records in the original data table and perform a calculation, but I can't figure out how to do it.
- sfmike99
Advocate II
Think I figured it out. As I said this failed because I was using a calculated table when DAX expected a single value:
('Raw Data'[Respondent] = (Target))But I found the "IN" command which produces the expected number of records:
('Raw Data'[Respondent] IN (Target))Very slick - lets me use CalculateTable and Intersect to build a list of matching respondents (with as many criteria as I like), then use the resulting table as a filter for other queries on the data.