Filtering on Text

[android1]

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

[Zubair_Muhammad · Accepted solution]

@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]

@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]

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 · Accepted solution]

@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]

@Zubair_Muhammad

Yeah, that works perfectly. Thanks again.

[Zubair_Muhammad · Accepted solution]

@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]

Holy Moly there's alot involved in that.

Thank you very much for this. Greatly appreciated.