Exclude all rows where each line of field "call type" has the same value against unique id field

[rashidanwar]

Hi everybody

I am trying to solve the below problem with the dax.

I have a table (call_data) with the following structure. Now I want to create a new table by excluding those lines where all the records in call_type gainst a unique call_id is "local". In the below table I want to exclude lines 4, 5 and 6 because in call_type field are the records against the unique call_id "456789" is local. 

I don't want to remove any line from 1 to 4. The reason is that not all the call_type against the call_id "123456" are "local". "in", "out" records are there. So we have to retain all the records from line 1 to line 4.

line_id             call_id                 call_type              start_time          
1                     123456               in                         12:55:45                     
2                     123456               out                       12:55:50
3                     123456               local                     12:56:05
4                     123456               local                     12:56:20
5                     456789               local                     12:55:45
6                     456789               local                     12:55:45
7                     456789               local                     12:55:45


I would much apprecaite any help in this regard
Thank you!
Rashid

[rashidanwar]

Hi everybody

I am trying to solve the below problem with the dax.

I have a table (call_data) with the following structure. Now I want to create a new table by excluding those lines where all the records in call_type gainst a unique call_id is "local". In the below table I want to exclude lines 4, 5 and 6 because in call_type field are the records against the unique call_id "456789" is local. 

I don't want to remove any line from 1 to 4. The reason is that not all the call_type against the call_id "123456" are "local". "in", "out" records are there. So we have to retain all the records from line 1 to line 4.

line_id             call_id                 call_type              start_time          
1                     123456               in                         12:55:45                     
2                     123456               out                       12:55:50
3                     123456               local                     12:56:05
4                     123456               local                     12:56:20
5                     456789               local                     12:55:45
6                     456789               local                     12:55:45
7                     456789               local                     12:55:45


I would much apprecaite any help in this regard
Thank you!
Rashid

[faisalazh]

hi,

you can create caculatetable with the following DAX

NewTable = filter('call_data', 'call_data'[call_id]="123456")

[rashidanwar]

Thank you @lbendlin exclude only 5, 6 and 7. Actually exclude the call_id 456789 because this call_id has all the call_type records of "local". The remaining data (output) should be in the form of  a separate table

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysnMS43PTNFJTszJgdMllQWpOsUliUUl8SWZuakKcKAUqxOtZKhjaGRsYmqmk5kHZFmZmlqZmCpgA2DVRjDV+aUlUOWmBmAZY5hMTj7QVrCcmZWBKVjOBJucEUSfqQ5QwtzCEkkO5ASwnBkeOXNccrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([call_type] = "local")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"call_id"}, {{"Rows", each _, type table [line_id=nullable text, call_id=nullable text, call_type=nullable text, #"start_time          "=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Rows]{0}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"line_id", "call_id", "call_type", "start_time          "}, {"line_id", "call_id", "call_type", "start_time          "}) & Table.SelectRows(#"Promoted Headers", each ([call_type] <> "local")),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"line_id", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"line_id", Order.Ascending}})
in
    #"Sorted Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[v-rongtiep-msft]

Hi @rashidanwar ,

I still have some issues.  If you want to keep 1 to 4,and remove 5,6 and 7? If call_type isn't local, then it will be deleted.

Please have a try.

Create a measure.

Measure2 = 
VAR result =
    CALCULATE (
        DISTINCTCOUNT ( call_data[  call_id ] ),
        FILTER (
            ALLSELECTED ( call_data ),
            call_data[  call_id ] = SELECTEDVALUE ( call_data[  call_id ] )
                && call_data[ call_type  ] <> "local"
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, 1 )

Then filter the measure.

If I have misunderstood your meaning, please provide your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LucMarv]

Great answer, finally an answer to really tackle the issue!

[rashidanwar]

Thank you @v-rongtiep-msft for your response. Actuall I want the output in the form of a table, not measure. 
same fields as a separate table excluding all those values where all the call_type records against a unique call_id is "local". 

[v-rongtiep-msft]

Hi @rashidanwar ,

Please create a calculated table based on the previously created measure as follows.

table = FILTER(call_data,[Measure2]=1)

If I have misunderstood your meaning, could you please provide a pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Do you want to do this at the level of the Query Editor or at the level of the visual?  Furthermore, i think you need to remove lines 5,6 and 7.  Am i correct?  When deciding which rows to remove, should one also look at the entries in the start_time column?  In the example that you have shared, if the start_time for line 5,6 and 7 were different, would we still have removed those lines?

[rashidanwar]

Hi @Ashish_Mathur Thank you for your response. 
The relevant columns are the call_id and call_type, and the output should be in the form of a separate table.
Exclude all those rows where all the (not some of them but all ) as you can see in case of call_id 456789 records against a unique call_id are "local" and create a new table for the remaining records.

[lbendlin]

Did you mean to say you want to exclude 4,5,6 and 7 ?