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 
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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.

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

      Hi,

      In Dax Query View, I think you missed EVALUATE statement.

       

      EVALUATE keyword (DAX) - DAX | Microsoft Learn

       

      Please try something like,

       

       

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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