Dynamic Cube.AttributeMember

[samdthompson]

Hello, I am pulling data from analysis services. In the initial steps i am wanting to limit the data by use of the calendar year. The years are dynamic so they could be 2022-2022 or 2021-2023 etc. Currently my code does this:

= Table.SelectRows(#"Added Items", each (
Cube.AttributeMemberId([Calendar.Cal Year]) = "[Calendar].[Cal Year].&["&Start_yyy&"]" meta [DisplayName = "2022"] or
Cube.AttributeMemberId([Calendar.Cal Year]) = "[Calendar].[Cal Year].&["&End_yyy&"]" meta [DisplayName = "2022"]))

This will only work for 2 years however. I would like to make this part of the query a between but clearly as soon as I reformat the fields as numbers, I loose the ability to fold which is a no go for the rest of the things i need to do.

How do I create a between type or a within a list argument and still allow the query to fold?

Cheers

[samdthompson · Accepted solution]

Agreed that the within list precludes query folding so is there a solution to mimic a between type filter or mimic a list type filter?

[v-yingjl]

Hi @samdthompson ,

For query folding, as far as I know, relational data source transformations that can be query folded are those that can be written as a single SELECT statement. A SELECT statement can be constructed with appropriate WHERE, GROUP BY, and JOIN clauses. It can also contain column expressions (calculations) that use common built-in functions supported by SQL databases.

See: Transformations that can achieve folding 

In this thread, I doubt whether create a between type or a within a list argument still supports query folding.

In addition, here is the similar thread which is also used Cube.AttributeMemberId and wanted to achieve it dynamically but within another way that you can refer:

Power Query - Get Data from AnalysisServices (Cube) - Filter with User-Function result 

Best Regards,
Community Support Team _ Yingjie Li

[samdthompson · Accepted solution]

Agreed that the within list precludes query folding so is there a solution to mimic a between type filter or mimic a list type filter?

[samdthompson · Accepted solution]

If anyone come across this the answer is yes, folding can continue if you use this M Code: 

Table.SelectRows(#"Added Items", each

List.Contains(FYS, //where the years in this case are a list

[Shift Date.Fin Year] //name of the cubeattribute

) = true)