Hi,
In my data set there is a value called (RAP MAC) and another column called (Device Type).
I need a new column of values that shows me what (RAP MAC) ID’s are Device Type = “Cisco Systems, Inc” only. Most of the (RAP MAC) ID’s have multiple Device Types.
Using filters i can sort of show the RAP MAC that only have 1 type, but i can’t take it to the next step which is showing it in a measure.
RAP EDNA.pbix (5.2 MB)
Are you tied to a DAX solution, since Power Query makes quick work of this problem.
Basically just Group By with All Rows:
And then filter any records that don’t have a Device Type Count = 1 and a Device Type = Cisco Systems
I actually did a video on this exact technique this morning. Incredibly flexible and powerful technique for these sorts of grouping problems.
I hope this is helpful. Full solution file posted below
– Brian
eDNA Forum RAP EDNA Solution.pbix (247.8 KB)
HI @chad.sharpe I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Hi @chad.sharpe, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @chad.sharpe, we’ve noticed that no response has been received from you since the 23rd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hey Brian,
Sorry for the delayed response. Work…
I’ve tried your solution and I’m not seeing it do what i need. i may not have explained it properly or I’m not viewing the output properly.
I’ve created a much smaller dataset (10 rows) and I’m going to try your solution on it today to see what i am missing. I’ve i can figure it out I’ll close out the solution, if not I’ll repost my efforts with the smaller dataset.
Thanks
Chad
Looking back, I can see how my solution post may not have been clear, since I kept the results in Power Query and didn’t pull them out into a separate visual. I’ve done that here, and unless I misinterpreted the problem I think this gives you just what you’re looking for – the RAP Mac addresses for devices with only one device type equal to Cisco Systems:
And here’s the M code I used to transform the Shelly table to get to this result:
let
Source = Csv.Document(File.Contents("C:\Users\brjul\Desktop\Shelly - (Hostname Mapping) Active Hosts Last Two Months (2).csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"RAP Mac", "Device Tyep"}, {{"AllData", each _, type table [#"RAP - (ACFID)"=nullable text, RAP Mac=nullable text, IP=nullable text, Device Tyep=nullable text, Host Mac=nullable text, #"Device Name (Consolidated)"=nullable text]}, {"Device Type Count", each Table.RowCount(_), Int64.Type}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"RAP - (ACFID)", "IP", "Host Mac", "Device Name (Consolidated)"}, {"RAP - (ACFID)", "IP", "Host Mac", "Device Name (Consolidated)"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded AllData", each ([Device Tyep] = "Cisco Systems, Inc") and ([Device Type Count] = 1)),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Device Type Count", "RAP Mac", "Device Tyep", "RAP - (ACFID)", "IP", "Host Mac", "Device Name (Consolidated)"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Device Tyep", "Device Type"}})
in
#"Renamed Columns"
If that isn’t what you need, please clarify and I’ll be glad to revise the solution.
Thanks.
– Brian
eDNA Forum RAP EDNA Solution.pbix (247.6 KB)
Hi @chad.sharpe, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @chad.sharpe, we’ve noticed that no response has been received from you since the 26th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @chad.sharpe, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!