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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors