Forum Discussion

android1's avatar
android1
Post Patron
7 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 tabl...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 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
    7 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] ) )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 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"
    )
android1's avatar
android1
Post Patron
7 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
    7 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] ) )
    )
    • android1's avatar
      android1
      Post Patron
      7 years ago

      Zubair_Muhammad

       

      Yeah, that works perfectly. Thanks again.