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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rashidanwar
Helper III
Helper III

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

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

1 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".

View solution in original post

10 REPLIES 10
faisalazh
Advocate II
Advocate II

hi,

you can create caculatetable with the following DAX

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

 

rashidanwar
Helper III
Helper III

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

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
Community Support
Community Support

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 )

 

11.PNG

Then filter the measure.22.PNG33.PNG

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.

 

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

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". 

Hi @rashidanwar ,

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

 

 

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

 

 

vpollymsft_0-1642757615936.png

 

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
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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