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.

     

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.