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

[wokka]

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.

[FreemanZ]

hi @wokka ,

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")
)

[Jihwan_Kim]

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"
)

[wokka]

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]

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"
)

[wokka · Accepted solution]

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]

Hi @wokka 

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 

[wokka]

Hi

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

[Poojara_D12]

Hi @wokka 

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 

[wokka]

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.