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

  • 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

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

     

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