Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
@AnandRanga In power bi you can use DAX expression as below,
=FILTER ('TABLENAME', 'TABLENAME'[Temparature] > 25)
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.
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
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |