none
Using a parameter in Table.SelectRows on a Analysis Services Cube source. Power Query. Excel. RRS feed

  • Question

  • I'm trying (power query in Excel) to use a parameter to filter data. It works fine if the data source is an Excel sheet, but when the data source is a cube, can't I make it work?

    I can't just insert my parameter as with the Excel source.

    An empty table is returned, it appears that the filter is on the parametername FAAR and not the contents of the FAAR parameter, how do I get the parameter value?

    How do I do it?

    Working fine:

    Table.SelectRows(#"Ændret type", each ([Year] = FAAR))

    Not working:

    Table.SelectRows(#"Tilføjede elementer", each Cube.AttributeMemberId([Regnskabs Periode.Regnskabs Aar]) = "[Regnskabs Periode].[Regnskabs Aar].&[FAAR]" meta [DisplayName = "2018"])

    Wednesday, October 16, 2019 11:16 AM

Answers

  • I have found the solution. FAAR should not be typed directly into the text string but inserted with & "

    = "[Regnskabs Periode].[Regnskabs Aar].&["&FAAR&"]"  insted of
    = "[Regnskabs Periode].[Regnskabs Aar].&[FAAR]"

    Makes it Work - simple.

    Thursday, October 17, 2019 9:10 AM

All replies

  • It looks like you're filter is trying to match the literal text value "[Regnskabs Periode].[Regnskabs Aar].&[FAAR]". What's an example of one of the actual values you're trying to match?

    Ehren

    Wednesday, October 16, 2019 11:04 PM
    Owner
  • Hi Ehren

    As an example, I assigned the value 2019 as text to the FAAR parameter.

    Regards

    Søren
    Thursday, October 17, 2019 6:33 AM
  • I have found the solution. FAAR should not be typed directly into the text string but inserted with & "

    = "[Regnskabs Periode].[Regnskabs Aar].&["&FAAR&"]"  insted of
    = "[Regnskabs Periode].[Regnskabs Aar].&[FAAR]"

    Makes it Work - simple.

    Thursday, October 17, 2019 9:10 AM