topic Re: Trying to select one columns value based on another column value in same table in DAX Commands and Tips

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

Anonymous — Mon, 02 Dec 2024 05:07:01 GMT

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.

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

Poojara_D12 — Mon, 02 Dec 2024 05:29:05 GMT

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

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

Jihwan_Kim — Mon, 02 Dec 2024 05:29:12 GMT

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

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

Anonymous — Mon, 02 Dec 2024 05:34:56 GMT

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

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

Poojara_D12 — Mon, 02 Dec 2024 05:38:21 GMT

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

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

Anonymous — Mon, 02 Dec 2024 06:06:28 GMT

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.

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

FreemanZ — Mon, 02 Dec 2024 09:18:02 GMT

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

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

Anonymous — Mon, 02 Dec 2024 11:57:58 GMT

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.

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

Jihwan_Kim — Mon, 02 Dec 2024 17:44:39 GMT

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

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

Anonymous — Tue, 03 Dec 2024 06:29:35 GMT

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