Forum Discussion
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...
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
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] ) ) )
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.
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] ) )
)