Forum Discussion

atziovara's avatar
atziovara
Helper I
2 years ago
Solved

Filters based on IDs

I have a table called 'TableBI' which stores the answers of multiple companies to multiple questions. It looks like this:   Company ID Question Answer 1 Current Demand 0.3 1 Future De...
"DAX help"
"Need Help"
'Need help'
'Need help' 'DAX'
@NeedHelp
  • hackcrr's avatar
    hackcrr
    2 years ago

    Hi, atziovara 

    Thank you very much for your reply. I have re-edited my DAX expression below:

    AnswerFiltered =
VAR _table =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Question] = "Future Demand" && 'Table'[Answer] > 0.2 ),
        'Table'[Company ID]
    )
RETURN
    IF ( 'Table'[Company ID] IN _table, 'Table'[Answer] )

    The results are as follows:

     

    Best Regards,

    hackcrr

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

hackcrr's avatar
hackcrr
Memorable Member
2 years ago

Hi, atziovara 

You can try the following DAX expressions:

AnswerFiltered = 
IF('Table'[Question]="Future Demand"&&'Table'[Answer]>0.2,'Table'[Answer])

This will filter out all answers with [Question] = Future Demand, [Answer] > 0.2. The results are as follows:

 

 

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

atziovara's avatar
atziovara
Helper I
2 years ago

hackcrr Thank you for your answer!

I am afraid that your response does not resolve my issue. As stated above, I would like my new column to include all answers of companies for which when [Question] = Future Demand, [Answer] > 0.2. Not just the answers to Future Demand where the value is > 0.2

It should look like this:

CompanyID Question Answer FilteredAnswer
1Current Demand0.3 
1Future Demand0.2 
1Current Labor0.4 
1Future Labor0.5 
2Current Demand0.50.5
2Future Demand0.40.4
2Current Labor0.30.3
2Future Labor0.20.2
3Current Demand0.30.3
3Future Demand0.30.3
3Current Labor0.60.6
3Future Labor0.70.7
  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, atziovara 

    Thank you very much for your reply. I'm a little confused as to why the
    CompanyID=1 is empty for the corresponding FilteredAnswer column?

     

    Best Regards,

    hackcrr

    • atziovara's avatar
      atziovara
      Helper I
      2 years ago

      hackcrr The data for the company whose ID = 1 are empty, because I would like to perform calculations for all the other companies that fit the criteria. I want to filter out companies based on the criteria related to Future Demand and then perform calculations with the answers of the remaining companies.

      • hackcrr's avatar
        hackcrr
        Memorable Member
        2 years ago

        Hi, atziovara 

        Thank you very much for your reply. I have re-edited my DAX expression below:

        AnswerFiltered =
VAR _table =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Question] = "Future Demand" && 'Table'[Answer] > 0.2 ),
        'Table'[Company ID]
    )
RETURN
    IF ( 'Table'[Company ID] IN _table, 'Table'[Answer] )

        The results are as follows:

         

        Best Regards,

        hackcrr

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.