cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sfmike99
Advocate II
Advocate II

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.

1 ACCEPTED SOLUTION
sfmike99
Advocate II
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. 

 

View solution in original post

3 REPLIES 3
sfmike99
Advocate II
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. 

 

sfmike99
Advocate II
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
    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. 

 

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors