Edit query condition

[SRK_23]

Hi, 

I've got an issue with a table that is too hughe to load (direct query to a SSAS tabular cube), my table is "KPI" in the screenshot below. 

How can I say "load only data after January 1st 2018 in the KPI table" ? What/where should I type that condition ?

edit: the "Date" dimension contain temporal information.

I suppose I have to add my condition somewhere in the advance query of the KPI table, but I don't know where and what to type. at the moment i have:

let
Source = AnalysisServices.Database("51.254.197.64", "ssas_tabular_sticky", [TypedMeasureColumns=true]),
Modèle = Source{[Id="Modèle"]}[Data],
Admin1 = Modèle{[Id="Admin"]}[Data],
#"Added Items" = Cube.Transform(Admin1,
{
{Cube.AddAndExpandDimensionColumn, "[Advertiser]", {"[Advertiser].[Advertiser ID].[Advertiser ID]"}, {"Advertiser.Advertiser ID"}},
{Cube.AddAndExpandDimensionColumn, "[Buyer platform]", {"[Buyer platform].[Buyer platform ID].[Buyer platform ID]"}, {"Buyer platform.Buyer platform ID"}},
{Cube.AddAndExpandDimensionColumn, "[Date]", {"[Date].[Year Month].[Year Month]"}, {"Date.Year Month"}},
{Cube.AddAndExpandDimensionColumn, "[DSP]", {"[DSP].[DSP ID].[DSP ID]"}, {"DSP.DSP ID"}},
{Cube.AddAndExpandDimensionColumn, "[Private Exchange]", {"[Private Exchange].[Private Exchange Billing Country].[Private Exchange Billing Country]"}, {"Private Exchange.Private Exchange Billing Country"}},
{Cube.AddAndExpandDimensionColumn, "[Trading desk]", {"[Trading desk].[Trading desk ID].[Trading desk ID]"}, {"Trading desk.Trading desk ID"}},
{Cube.AddMeasureColumn, "Adserved Revenue", "[Measures].[Adserved Revenue]"},
{Cube.AddMeasureColumn, "DSP Revenue (in Corporate Currency)", "[Measures].[DSP Revenue (in Corporate Currency)]"},
{Cube.AddMeasureColumn, "Impressions", "[Measures].[Impressions]"},
{Cube.AddMeasureColumn, "Programmatic Revenue", "[Measures].[Programmatic Revenue]"},
{Cube.AddMeasureColumn, "Requests", "[Measures].[Requests]"},
{Cube.AddMeasureColumn, "SSP Revenue (in Corporate Currency)", "[Measures].[SSP Revenue (in Corporate Currency)]"},
{Cube.AddMeasureColumn, "SSP Revenue (in PX Currency)", "[Measures].[SSP Revenue (in PX Currency)]"}
}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Items",{{"Date.Year Month", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date.Year Month] <> 201601 and [Date.Year Month] <> 201602 and [Date.Year Month] <> 201603 and [Date.Year Month] <> 201604 and [Date.Year Month] <> 201605 and [Date.Year Month] <> 201606 and [Date.Year Month] <> 201607 and [Date.Year Month] <> 201608 and [Date.Year Month] <> 201609 and [Date.Year Month] <> 201610 and [Date.Year Month] <> 201611 and [Date.Year Month] <> 201612)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Advertiser.Advertiser ID", "dim.Advertiser ID"}, {"Buyer platform.Buyer platform ID", "dim.Buyer platform ID"}, {"DSP.DSP ID", "dim.DSP ID"}, {"Private Exchange.Private Exchange Billing Country", "PX Billing Country"}, {"Trading desk.Trading desk ID", "dim.Trading desk ID"}, {"Date.Year Month", "dim.Year Month"}})
in
#"Renamed Columns"

[v-juanli-msft]

Hi @SRK_23

The screenshot you can't write MDX or DAX occurs when you try to switch SSAS connect model from "Connect Live" to "Import", i'm afraid it is not possible to do that in power bi desktop currently, you could vote the idea here.

support SSAS connect model from "Connect Live" to "Import"

Also when you connect live to SSAS, it doesn't support to connect another data source in import mode in the same pbix.

If you could accept to reconnect to the SSAS, you can consider the method of another thread of yours.

If not, you may try to select rows in Queries editor so that it would load less data into data model.

In the following code, add the bold code

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] > 2018/1/1 and 
[Date.Year Month] <> 201601 and [Date.Year Month] <> 201602 and [Date.Year Month] <> 201603 and 
[Date.Year Month] <> 201604 and [Date.Year Month] <> 201605 and [Date.Year Month] <> 201606 and 
[Date.Year Month] <> 201607 and [Date.Year Month] <> 201608 and [Date.Year Month] <> 201609 and 
[Date.Year Month] <> 201610 and [Date.Year Month] <> 201611 and [Date.Year Month] <> 201612))

Best regards

Maggie

Best regards

Maggie

[v-juanli-msft]

Hi @SRK_23

When you select "Import", you could apply SQL query in the SQL statement box to filter the data loading into Power BI.

Execute SQL Server Stored Procedure With User Parameter In Power BI

Best Regards

Maggie

[SRK_23]

Hi-- thanks for taking the time to answer.

In the import section, the dax field is grey, I can't type, any idea how to do it ?