Forum Discussion

sfmike99's avatar
sfmike99
Icon for Advocate II rankAdvocate II
4 years ago
Solved

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 In...
Need Help
  • sfmike99's avatar
    sfmike99
    4 years ago

    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. 

     

sfmike99's avatar
sfmike99
Icon for Advocate II rankAdvocate II
4 years ago

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
    MedianBudget

So 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.