Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.