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 column1 from table1 where column2 = 'fgh'

 

Ive tried the following, but with no success :

 

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

 

Return 

    Output

 

Any help appreciated. DAX seems to be a very different world......

 

Thanks in advance.

 

  • 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 

9 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but I assume your expected result is table.

    If it is, please try something like below.

    Please check the below picture and the attached pbix file.

     

    Table1

     

     

    SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn

     

    expected result table = 
CALCULATETABLE (
    SELECTCOLUMNS ( Table1, Table1[column1] ),
    Table1[column2] = "fgh"
)
     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Jihwan_kim and everyone,

       

      So i had a look at the pbix file, thank you. 

      If you go into the DAX query view, and run EVALUATE TOPN(100, 'Table1')   you see the contents of Table1 listed OK. 

      DAX Query Editor can see Table 1 and list its contents just fine.

       

      Assumption - I can run the below code in DAX Query Editor in the pbix file to generate the temporary table called expectedresulttable ( so I dont overwrite the existing table "expected result table")  

       

      expectedresulttable =

      CALCULATETABLE (
          SELECTCOLUMNS ( Table1, Table1[column1] ),
          Table1[column2] = "fgh"
       
      And every single time I run it, I get this error:
       

      Query (3, 1) The syntax for 'CALCULATETABLE' is incorrect. ( expectedresulttable = CALCULATETABLE ( SELECTCOLUMNS ( Table1, Table1[column1] ), Table1[column2] = "fgh" )).

       


       

      Is this expected behaviour?

       

      Can you please explain where / how eactly did you run the code ( above ) to generate the expected result table - which screen etc did you use please? Maybe I have missed something?

       

      I need to get this running for work, so any help appreciated.

       

      Thank you in advance.

  • Poojara_D12's avatar
    Poojara_D12
    Super 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
        Super 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 

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi Anonymous ,

     

    not sure if i fully get you, try to create a calculated table like:

     

    Calculated Table =
    CALCULATETABLE(
    VALUES('Table1'[column1])
    FILTER(Table1','Table1'[column2] = "fgh")
    )