Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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] ) ) )
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" )
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] ) ) )
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
Holy Moly there's alot involved in that.
Thank you very much for this. Greatly appreciated.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |