dax query when importing data

[MP_123]

hi

can i use dax query to import data to PBI just from 2016 for example?

if yes, how?

thanks a lot

[Greg_Deckler]

You would use power query and technically "M" not DAX, but yes. Edit your query or create a new query and connect to your data and in your "Year" column, click the drop down arrow and uncheck everything but 2016. The actual query will look something like:

let
    Source = Csv.Document(File.Contents("C:\temp\azureml\SF_Incident_Data_Prepped.csv"),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"IncidntNum", Int64.Type}, {"Category", type text}, {"DayOfWeek", type text}, {"Date", type date}, {"Time", type time}, {"PdDistrict", type text}, {"Resolution", type text}, {"Address", type text}, {"X", type number}, {"Y", type number}, {"Location", type text}, {"Resolved", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Hour", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = 2016))
in
    #"Filtered Rows"

If you don't see all of your Year choices, you may have a little yellow box that says something like "List may be incomplete" and in that case you click the "Load more" link.

[MP_123]

@Greg_Deckler

hi,

what do you mean by "year choice"? all the possible year in my DB?

and another questions, i wanted to filter in the first place because i can't import all my data to the PBI - says it's out of memory or i am using 32 bit version (i'm using 64bit), actually when i deselect some of my dimensions the data is do imported but still thewre are missing rows

the memory limitiation i deal with, is it because of my computer's memory? or the PBI size limit?

thanks a lot

[v-sihou-msft]

Hi @MP_123,

Please check the size of your .pbix file, it has 250 MB limitation.

Please refer to links below:

Power BI Workbook Size Limitations.

Publishing small pbix files, what happens when the data model grows beyond 250 meg after refresh?

Regards,

[MP_123]

thank you @v-sihou-msft

i need to clarify something:

my pbix file can be greater then 250Mb, but the problem begins when i want to publish it, right?

[Greg_Deckler]

This is in the query editor window, so you are only seeing a preview of the data. When you use the drop down arrow in your Year column (assuming you have a year column) you should see a list of all of the distinct values that are in that column and you can select the ones that you want.

[asocorro]

You can't use DAX when importing data.  The most efficient way of implementing such as filter is at the data source level (i.e., by putting a WHERE clause in your query, if using SQL Server, for example).  If that's not possible, you are going to have to bring in all the data and then filter it in PBI.  One way is to do it in M (Edit Queries - Power Query).  Another is to create a new table using DAX.  And a third way is just to add report- or page-level filters.