how to write this expression in PowerBI ?

[AnandRanga]

How to write this expression in PowerBI

select distinct([date]),Temperature from Device47A8F where Temperature>25

Totally new to PowerBI. Is there any tool that can change the query from sql to PowerBI expression?

I have tried so many type of different type of expressions but getting error, Most of the time I am getting this:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Need help, Thanks

[v-haibl-msft · Accepted solution]

@AnandRanga

If you want to get the distinct number of date which temperature is above 25. You can create a measure with following expression. And drag this measure into a Card visual.

Measure = 
CALCULATE ( DISTINCTCOUNT ( Device47A8F[date] ), Device47A8F[Temperature] > 25 )

If you want to show the distinct date which temperature is above 25. You can create a calculated table with following expression. And drag the column of this new table into a Table visual.

Table = 
CALCULATETABLE ( VALUES ( Device47A8F[date] ), Device47A8F[Temperature] > 25 )

I’ve uploaded my .pbix file here for reference.

Best Regards,

Herbert

[ankitpatira]

@AnandRanga In power bi you can use DAX expression as below,

=FILTER ('TABLENAME', 'TABLENAME'[Temparature] > 25)

[AnandRanga]

@ankitpatira

It gives the below error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I have tried writing this in measure and column both. 

[BhaveshPatel]

You should download DAX Studio and connect your model to it to view the results of the DAX Calculation suggested by Ankit.

DAX can also be used as a query language.

Or You can put on a filter at the Query mode in PowerBI.

FYI :Use Evaluate at the start of the DAX syntax if you are using DAX Studio.

Thanks & Regards,

Bhavesh

[AnandRanga]

@BhaveshPatel

Thanks for telling me about DAX studio, I installed it but facing the issue while i am trying to connect with data source. In tabular server i am typing the instance name of my sql server and it's showing the below error :

A connection cannot be made to redirector ensure that sql browser service is running.

While service is running and I have tried changing the ports also, i changed it to some specific port number from dynamic ports, but it didnt work.

Any solution ?