Using filter on UNION

[kjartank]

Hi.

I have been using the "UNION" function to get my charts to be more manageable and am in need of a filter on these tables. The code I've been using looks like this.

Satisfaction Score = UNION (
SELECTCOLUMNS (Tabel1; "Attribute"; "01. Check-in"; "Answer"; Tabel1[Check-in] );
SELECTCOLUMNS (Tabel1; "Attribute"; "02. Room"; "Answer"; Tabel1[Room] );
SELECTCOLUMNS (Tabel1; "Attribute"; "03. Service"; "Answer"; Tabel1[Service] );
SELECTCOLUMNS (Tabel1; "Attribute"; "04. Entertainment"; "Answer"; Tabel1[Entertainment] );
SELECTCOLUMNS (Tabel1; "Attribute"; "05. Cleanliness"; "Answer"; Tabel1[Cleanliness] );
SELECTCOLUMNS (Tabel1; "Attribute"; "06. Childfriendly facilities"; "Answer"; Tabel1[Chilfriendly facilities] );
SELECTCOLUMNS (Tabel1; "Attribute"; "07. Prices"; "Answer"; Tabel1[Prices] );
SELECTCOLUMNS (Tabel1; "Attribute"; "08. Overall experience"; "Answer"; Tabel1[Overall Experience] )
 )

I need to filter ALL the variables on the highest value of a variable called "Period". How would I go about implementing that in the UNION function?

Thanks in advance.

[kjartank]

Hi @v-huizhn-msft and @Greg_Deckler

An embarrasing fault from my side. The solution that @Greg_Deckler works just fine. I should have replaced the comma with a semicolon, which I didn't do at first. So please accept my apologies. Thanks a lot. It runs beautifully now!

[Greg_Deckler]

@kjartank - Apologies for the comma versus semi-colon! Still haven't mastered those localization differences!

[v-huizhn-msft]

Hi @kjartank,

Please follow the @Greg_Deckler said, filter the table using the highest value of a variable called "Period". Then use the filter table in your formula, don't hesitate to ask if you have any other issue.

Best Regards,
Angelia

[kjartank]

hi @v-huizhn-msft

I've tried the solution that @Greg_Deckler suggested and I got an error. I'm not sure why, but the error message reads "Cannot identify the table that contains [period] column."

[Greg_Deckler · Accepted solution]

You would use FILTER in your first parameter. So,

SELECTCOLUMNS (FILTER(Table1,[Period] = MAX([Period])); "Attribute";...

Something along those lines. Would need sample data to get specfic.

You might also want to create a variable where you filter your table and return it and then use that variable in place of Table1 in your SELECTCOLUMNS. Would be more efficient that way.

[bsas]

Hi @Greg_Deckler,

How I can create such variable (filtered table based on slicer) and then use it selectcolumns?