Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Trying to select one columns value based on another column value in same table

Hi   I'm very used to working with SQL Server, and I am trying to return the values of one column on a powerbi table based on text values of another column in the same table  , e.g.   select colu...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Jihwan_Kim

     

    Thank you !  

     

    Adding EVALUATE did the trick and the code below now executes from DAX Query View inside the powerbi file. It will select the rows in [column1] where the value in [column2] has the text value of  "fgh"  .

     

    EVALUATE
    CALCULATETABLE (
    SELECTCOLUMNS ( Table1, Table1[column1] ),  Table1[column2] = "fgh"
    )

     

     

     

    I investigated further on how to create a temp table ( dynamic table ) using the same code. 

    To do this I opened up Table View, pasted in the following and clicked the green tick ( note that the EVALUATE function is removed for this step ) :

     

    Temp_Table1 =
    CALCULATETABLE (
    SELECTCOLUMNS ( Table1,  "Test_Column1",  Table1[column1] ),  Table1[column2] = "fgh"
    )
     

     

    This then creates a temp/dynamic table called Temp_table1 with column called "Test_Column1" you should see appear in thepowerbi  Data  pane. 
     
     
    To confirm everything was all working, I then went to the Report View in powerbi, found the Temp_Table1 and dragged the Test_Column1 into the report area, and it accurately showed the contents of the column as expected.
     
     
    Kudos for this.
     
     
     ------------------------
    Subject :    Trying to select one columns value based on another column value in same table
     
    Keywords   DAX  powerbi temp table dynamic table column value select into 
Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

Hi Anonymous 

Try using the below dax:

Output = 
CALCULATE(
    SELECTEDVALUE('Table1'[column1]),
    'Table1'[column2] = "fgh"
)

This will return the value of column1 where column2 = "fgh". If there are multiple matches, SELECTEDVALUE will return blank. Use CONCATENATEX if you expect multiple results.

 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi

 

Thank you, although it says the calculate syntax is incorrect?

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

    apologise for that, can you please try this:
    Output =
    CALCULATE(
    SELECTEDVALUE('Table1'[column1]),
    FILTER('Table1', 'Table1'[column2] = "fgh")
    )

    Did I answer your question? Mark my post as a solution, this will help others!

    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi

       

      Sorry, still gives syntax error. 

       

      Below is direct copy and paste of all the code , from within DAX query view exactly as it appears in my desktop powerbi :

       

       

      Output =
          CALCULATE(  
                     SELECTEDVALUE('Table1'[column1]),
                     FILTER('Table1', 'Table1'[column2] = "fgh")
          )
       
       
      Thanks for your help so far.