Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
android1
Post Patron
Post Patron

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

 

 

 

 

 

2 ACCEPTED SOLUTIONS

@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" )

 

filterigontext.png 

 

File attached as well


Regards
Zubair

Please try my custom visuals

View solution in original post

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

Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

@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"
    )

Regards
Zubair

Please try my custom visuals

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.

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

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

Yeah, that works perfectly. Thanks again.

@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" )

 

filterigontext.png 

 

File attached as well


Regards
Zubair

Please try my custom visuals

Holy Moly there's alot involved in that.

 

Thank you very much for this. Greatly appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.