Forum Discussion

Icon for Helper III rank

Helper III

9 years ago

Solved

how to write this expression in PowerBI ?

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
9 years ago
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

5 Replies

ankitpatira
Community Champion
9 years ago
AnandRanga In power bi you can use DAX expression as below,

=FILTER ('TABLENAME', 'TABLENAME'[Temparature] > 25)
- AnandRanga
  Helper III
  9 years ago
  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
    Super User
    9 years ago
    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
v-haibl-msft
Microsoft Employee
9 years ago
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

Related content