How to filter an Olap Cube on Power Query Side

[Anonymous]

Hi

I need to connect my report to an Olap Cube, so I use "Analysis Services" connector and it works fine but unfortunatly I want to filter my data before loading it on Power BI.

My dataset contains more than 16 Million of rows and I just need 1 Million 😉

On the cube Navigation view I can just add Dimension or Measure and I don't have any filter proposal

So I try to use Cube.ApplyParameter on the advanced editor but it seems that I'm not enought advanced myself.

This first test :

let
    Source = AnalysisServices.Databases("xxxxxxxxxx\Z2347PROD14", [TypedMeasureColumns=true, Implementation="2.0"]),
    ISI_2_1 = Source{[Name="ISI_2_1"]}[Data],
    TimeTracking_tech = ISI_2_1{[Id="TimeTracking_tech"]}[Data],
    #"Time Tracking1" = TimeTracking_tech{[Id="Time Tracking"]}[Data],
    #"Éléments ajoutés" = Cube.Transform(#"Time Tracking1",
        {
            {Cube.AddAndExpandDimensionColumn, "[Members]", {"[Members].[MEM member].[MEM member]", "[Members].[MEM Organization].[MEM Organization]"}, {"Members.MEM member", "Members.MEM Organization"}},
            {Cube.ApplyParameter("[Members].[MEM Organization].[MEM Organization]",{"Digital Front End - DT"})}           
        }),
    #"Lignes filtrées1" = Table.SelectRows(#"Éléments ajoutés", each (Cube.AttributeMemberId([Members.MEM Organization]) = "[Members].[MEM Organization].&[73847]" meta [DisplayName = "Digital Front End - DT"]))
in
    #"Lignes filtrées1"

raise this error :

Expression.Error : Désolé... Nous n'avons pas pu convertir la valeur "[Members].[MEM Organ..." en type Table.
Détails :
Value=[Members].[MEM Organization].[MEM Organization]
Type=[Type]

and this other test :

let
    Source = AnalysisServices.Databases("xxxxxxxxxx\Z2347PROD14", [TypedMeasureColumns=true, Implementation="2.0"]),
    ISI_2_1 = Source{[Name="ISI_2_1"]}[Data],
    TimeTracking_tech = ISI_2_1{[Id="TimeTracking_tech"]}[Data],
    #"Time Tracking1" = TimeTracking_tech{[Id="Time Tracking"]}[Data],
    Test = Cube.ApplyParameter(#"Time Tracking1","[Members].[MEM Organization].[MEM Organization]",{"Digital Front End - DT"}),
    #"Éléments ajoutés" = Cube.Transform(Test,
        {
            {Cube.AddAndExpandDimensionColumn, "[Members]", {"[Members].[MEM member].[MEM member]", "[Members].[MEM Organization].[MEM Organization]"}, {"Members.MEM member", "Members.MEM Organization"}}            
        }),
    #"Lignes filtrées1" = Table.SelectRows(#"Éléments ajoutés", each (Cube.AttributeMemberId([Members.MEM Organization]) = "[Members].[MEM Organization].&[73847]" meta [DisplayName = "Digital Front End - DT"]))
in
    #"Lignes filtrées1"

raise this other error :

Expression.Error : La clé ne correspondait à aucune ligne dans la table.
Détails :
Key=
Id=[Members].[MEM Organization].[MEM Organization]
Table=[Table]

If somebody have an idea, it will be very helpfull 😉

nb : I can't use direct connect because I need to use other external data 😉

[Kevin23]

Hello Stephane,

I have the same issue.

Did you find the solution?

Kevin

[Anonymous]

Hi Kevin,

Yes i found the solution by customing MDX query

It's little ticky beacause syntax doesn't look like SQL but it works fine

[Kevin23]

Thank you Stephane,

I want to do it directly from Power BI to Google Analytics. So i'm going to look for somethink in power Query, directly to GA.

Kevin

[lbendlin]

When you connect to Analysis Services you can specify a custom MDX query. Use that for your filtering.

[Anonymous]

Good idea but if I want to use MDX request I need to define the database and unfortunatly I didn't able to do that

Actually on the navigator side I have that view :

I try the following configuration :

• ISI_2_1
• [ISI_2_1]
• ISI_2_1.TimeTracking_tech
• [ISI_2_1].[TimeTracking_tech]
• ISI_2_1.TimeTracking_tech.Time Tracking
• [ISI_2_1].[TimeTracking_tech].[Time Tracking]

But each time I got the same error : " Either the xxx user does not have access to the xxxxx database, or the database does not exist."