Forum Discussion

kjartank's avatar
kjartank
Helper II
9 years ago
Solved

Using filter on UNION

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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's avatar
      bsas
      Post Patron
      8 years ago

      Hi Greg_Deckler,

       

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      kjartank
      Helper II
      9 years ago

      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."

  • kjartank's avatar
    kjartank
    Helper II
    9 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      9 years ago

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