Forum Discussion

android1's avatar
android1
Post Patron
8 years ago
Solved

Filtering on Text

Hi,

 

I have a table with 2 columns, Client ID, Client Name & Bill Payer. The Bill Payer always contains the word Private or HSE.

 

Each client has 1 or 2 Bill Payers.

 

I need to return 2 tables.

 

Table A that shows all clients who only have the Bill Payer that conatins the text Private. (If they have a bill payer containing the word Private & a Bill Payer containing the word HSE I don't want them in this table)

 

Table B that shows all clients who have a Bill Payer containing the word Private or HSE or both.

 

See sample file.

 

https://www.dropbox.com/s/vpb5kcezhf7ftdy/Sample%20Data.xlsx?dl=0

 

 

 

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    android1

     

    Then

     

    Private & HSE both =
VAR orginalTable =
    SUMMARIZECOLUMNS ( Table1[Client ID], Table1[Client Name] )
VAR PrivateOnlyTable =
    SUMMARIZECOLUMNS ( 'Private only Table'[Client ID],
    'Private only Table'[Client Name] )
RETURN
    ADDCOLUMNS ( EXCEPT ( orginalTable, PrivateOnlyTable ), "Type", "HSE" )

     

     

     

    File attached as well

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    android1

     

    I think this will work

    Otherwise show me some sample data with date

     

    Private only Table =
VAR temp =
    ADDCOLUMNS (
        Table1,
        "Private", SEARCH ( "Private", [Bill Payer], 1, 0 ),
        "HSE", SEARCH ( "HSE", [Bill Payer], 1, 0 )
    )
VAR temp1 =
    SUMMARIZE (
        temp,
        Table1[Client ID],
        Table1[Client Name],
        "Private", SUMX ( FILTER ( temp, [Client ID] = EARLIER ( [Client ID] ) ), [Private] ),
        "HSE", SUMX ( FILTER ( temp, [Client ID] = EARLIER ( [Client ID] ) ), [HSE] )
    )
VAR temp2 =
    FILTER ( temp1, [Private] > 0 && [HSE] = 0 )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( temp2, [Client ID], [Client Name] ),
            "Type", "Private"
        ),
        "Date", CALCULATE ( MIN ( Table1[Date] ) )
    )

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    android1

     

    Try this calculated table

    'From Modelling Tab>>new Table

     

    Private only Table =
VAR temp =
    ADDCOLUMNS (
        Table1,
        "Private", SEARCH ( "Private", [Bill Payer], 1, 0 ),
        "HSE", SEARCH ( "HSE", [Bill Payer], 1, 0 )
    )
VAR temp1 =
    SUMMARIZE (
        temp,
        Table1[Client ID],
        Table1[Client Name],
        "Private", SUMX ( FILTER ( temp, [Client ID] = EARLIER ( [Client ID] ) ), [Private] ),
        "HSE", SUMX ( FILTER ( temp, [Client ID] = EARLIER ( [Client ID] ) ), [HSE] )
    )
VAR temp2 =
    FILTER ( temp1, [Private] > 0 && [HSE] = 0 )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( temp2, [Client ID], [Client Name] ),
        "Type", "Private"
    )
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      android1

       

      Then

       

      Private & HSE both =
VAR orginalTable =
    SUMMARIZECOLUMNS ( Table1[Client ID], Table1[Client Name] )
VAR PrivateOnlyTable =
    SUMMARIZECOLUMNS ( 'Private only Table'[Client ID],
    'Private only Table'[Client Name] )
RETURN
    ADDCOLUMNS ( EXCEPT ( orginalTable, PrivateOnlyTable ), "Type", "HSE" )

       

       

       

      File attached as well

      • android1's avatar
        android1
        Post Patron
        8 years ago

        Holy Moly there's alot involved in that.

         

        Thank you very much for this. Greatly appreciated.

    • android1's avatar
      android1
      Post Patron
      8 years ago

      Hi Zubair_Muhammad,

       

      One last thing. How would I add a column called Enddate to Private only Table?

      This is a date columns from Table1.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        android1

         

        I think this will work

        Otherwise show me some sample data with date

         

        Private only Table =
VAR temp =
    ADDCOLUMNS (
        Table1,
        "Private", SEARCH ( "Private", [Bill Payer], 1, 0 ),
        "HSE", SEARCH ( "HSE", [Bill Payer], 1, 0 )
    )
VAR temp1 =
    SUMMARIZE (
        temp,
        Table1[Client ID],
        Table1[Client Name],
        "Private", SUMX ( FILTER ( temp, [Client ID] = EARLIER ( [Client ID] ) ), [Private] ),
        "HSE", SUMX ( FILTER ( temp, [Client ID] = EARLIER ( [Client ID] ) ), [HSE] )
    )
VAR temp2 =
    FILTER ( temp1, [Private] > 0 && [HSE] = 0 )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( temp2, [Client ID], [Client Name] ),
            "Type", "Private"
        ),
        "Date", CALCULATE ( MIN ( Table1[Date] ) )
    )