Only show data form yesterday, error message

[Paritäa]

I've been trying to filter my data in order to only show data from yesterday. I tried it in Power Query where I import the dataset and I've tried it in DAX.

It never works. I always receive the error message in DAX "The expression refers to multiple columns. Multiple columns cannot be converted to one scalar value." for two methods:

1. 

Measure = FILTER('SM-Daten', 'SM-Daten'[TimestampUtc] >= UTCTODAY()-1)

2.

Filtered = 
'SM-Daten' =
CALCULATETABLE (
    'Filtered',
    DATESBETWEEN ('SM-Daten'[TimestampUtc], TODAY() - 1, TODAY() - 1)
)

I don't get why the program thinks that I refer to multiple columns!</p><p>The methods that I tried in Power Query also didn't work. I prefer DAX but if you have a better idea in Power Query I can</p>

[LQuedas]

hey @Paritäa ,

don't understand what you are trying to do, the two DAX functions you used, filter and CalculateTables, return a table, and you are trying to assign a table to what I'm assuming is a measure... that's why you are getting that error message...

Can you be more specific about what you are trying to do, what measure are you trying to calculate?

Cheers, LQ 

[Paritäa]

I don't really know what I have to do. I did some research and found these option by either creating a measure or new table to filter the data. 

This data will be automated as new data is measured every day. But I only need to visualize the data from yesterday. I'm just trying to add a function so that the  original dataset is reduced to measurements from yesterday. 

[LQuedas]

Hey @Paritäa ,

you can use the Power Query Editor to filter the dataset, as example, I created a small table with today and yesterday rows:

then I've edited the query and added the date filter:

with this filter the today data is removed from the table.

Hope this works for you.

Cheers, LQ

[Paritäa]

I get the following error:

Expression.Error: The field 'date' of the record wasn't found.

A friend of mine said I should try with daytime but the same error appears...

[LQuedas]

Hey @Paritäa ,

the field 'date' only exists in my example, you need to adapt the code to your situation, I don't know the names of your dataset's columns name.

Cheers, LQ

[Paritäa]

Hi @LQuedas 

I did change them. But is it correct that < #date, does not refer to the column, right?

[LQuedas]

Hey @Paritäa 

let's try step by step 🙂

1) I created a new dataset with a column named TimeStampDate that I want to filter

2) I added a filter to the TimeStampDate Column to load all the rows except today's rows 

3) then I've edited the query with the advance editor

4) Finally, I Replaced the date #Date(2003,4,4) by the Year, Month and day of the UTCNow() function

Year - >  Date.Year(DateTimeZone.UtcNow())

Month - > Date.Month(DateTimeZone.UtcNow())

Day - > Date.Day(DateTimeZone.UtcNow())

This change will make the filter dynamic depending on the UTC now date.

Hope this now makes sense to you.

Cheers, LQ

[Paritäa]

Expression.Error: The column 'TimestampUtc' of the table wasn't found.
Details:
TimestampUtc
I got this error message before, but as you've seen at the screenshot there actually is a column named the exact name. I even copy pasted it to make sure...

[Paritäa]

This is what my table looks like: