Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community Members,
Please help me with a solution to this problem.
I have the following Data
Customer NameModelsSectorOps LeadGo-LIVE DateModel Type Framework
A | Model 1A | Sector A | Person A / Person B | Monday, March 26, 2018 | Old |
B | Model 1B | Sector A | Person C | Monday, July 30, 2018 | New |
B | Model 2B | Sector A | Person C | Monday, July 30, 2018 | New |
B | Model 2B | Sector A | Person C | Monday, July 30, 2018 | New |
C | Model 1C | Sector A | Person A / Person D | Thursday, November 1, 2018 | Old |
C | Model 2C | Sector A | Person A / Person D | Thursday, November 1, 2018 | Old |
C | Model 3C | Sector A | Person A / Person D | Thursday, November 1, 2018 | Old |
C | Model 4C | Sector A | Person A / Person D | Thursday, November 1, 2018 | Old |
C | Model 5C | Sector A | Person A / Person D | Thursday, November 1, 2018 | Old |
C | Model 6C | Sector B | Person A / Person D | Thursday, November 1, 2018 | Old |
C | Model 7C | Sector B | Person A / Person D | Thursday, November 1, 2018 | Old |
D | Model 1D | Sector B | Person A / Person B | Monday, January 14, 2019 | Old |
D | Model 2D | Sector B | Person A / Person B | Monday, January 14, 2019 | Old |
I have created the following Data Models
Output Needed:
I require slicers for Ops Lead.
When I select Person A, it should show me projects for related to Person A. Same should be for Person B, Person C and Person D.
Do let me know what transformations would be needed to acheive this.
Regards,
HN
Solved! Go to Solution.
Yeah, the duplication thing isn't good with that idea.
How about this:
Create a table with all the unique person values [opsLeadName] in, let's say it's called dimOpsLead. Keep this UNRELATED from your main table.
Use the dimOpsLead[opsLeadName] field in your slicer, then on the page/visuals you want to slice by the Ops Lead, you could use this measure as a filter:
opsLeadExists =
SEARCH(
SELECTEDVALUE(dimOpsLead[opsLeadName]),
SELECTEDVALUE(yourTable[Ops Lead]),
,-1
)
Set the filter to be [opsLeadExists] >= 1.
Pete
Proud to be a Datanaut!
In Power Query, I would create a new query something like this:
let
Source = Table.SelectColumns(yourTable, "Ops Lead"),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Ops Lead", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), {"OpsLeadTemp1", "OpsLeadTemp2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "OpsLeadList", each {[OpsLeadTemp1], [OpsLeadTemp2]}),
#"Expanded OpsLeadList" = Table.ExpandListColumn(#"Added Custom", "OpsLeadList"),
#"Filtered Rows" = Table.SelectRows(#"Expanded OpsLeadList", each ([OpsLeadList] <> null)),
distinctList = Table.Distinct(Table.SelectColumns(#"Filtered Rows", "OpsLeadList"))
in
distinctList
This will dynamically create a distinct list of all the people featured in your original [Ops Lead] field, using the same technique as my first answer (I knew it would come in useful somehow!).
Also, it looks lke you've accidentally accepted your own answer as the solution on this post, rather than mine.
Pete
Proud to be a Datanaut!
Hi @harshnathani ,
One way to do it would be to list the related parties then then expand the list to new rows, something like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdNBC4IwGAbgv/Kx8yA3zero9CRoQd3Ew9KBh+VgZuG/TyWalODF0e3l28cDe9myDAUIo0SVQgIZ4lkUd6VhiCehG1VDABt4Rzbu1iXvMCRcFxVQHwN1yL4/OMoS5ThDzIBsFgwnStzKDlzng6Ti+YXQPyOhuU641E/UTy9Vq5tRTNVD3K5CA/npyKDUBuraQD0b6NYG6k9Qtha6Ww+NzJOKltDpl4t53XLdAfFG8jBL0lXI/AU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, Models = _t, Sector = _t, #"Ops Lead" = _t, #"Go-LIVE" = _t, #"Model Type Framework" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Models", type text}, {"Sector", type text}, {"Ops Lead", type text}, {"Go-LIVE", type date}, {"Model Type Framework", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Ops Lead", "OpsLeadTemp"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "OpsLeadTemp", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), {"OpsLeadTemp1", "OpsLeadTemp2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "OpsLeadList", each {[OpsLeadTemp1], [OpsLeadTemp2]}),
#"Expanded OpsLeadList" = Table.ExpandListColumn(#"Added Custom", "OpsLeadList"),
#"Filtered Rows" = Table.SelectRows(#"Expanded OpsLeadList", each ([OpsLeadList] <> null))
in
#"Filtered Rows"
You could then slice on the new [OpsLeadList] field. Obviously this introduces duplicates into your table, but the technique could just as easily be used to create a new reference table of [Project Code], [OpsLeadList] or similar.
Pete
Proud to be a Datanaut!
Hey @BA_Pete ,
Thank You so much for the reply.
The issue which I see in my situation is that the dataset is quite huge and I also haves similar situation for some other columns too (Batch Time , Batch Size) .
As stated by you this introduces duplicates in my table which I want to avoid.
Is there any other way to acheive this?
Regards,
HN
Yeah, the duplication thing isn't good with that idea.
How about this:
Create a table with all the unique person values [opsLeadName] in, let's say it's called dimOpsLead. Keep this UNRELATED from your main table.
Use the dimOpsLead[opsLeadName] field in your slicer, then on the page/visuals you want to slice by the Ops Lead, you could use this measure as a filter:
opsLeadExists =
SEARCH(
SELECTEDVALUE(dimOpsLead[opsLeadName]),
SELECTEDVALUE(yourTable[Ops Lead]),
,-1
)
Set the filter to be [opsLeadExists] >= 1.
Pete
Proud to be a Datanaut!
Hi @BA_Pete ,
Thanks. I will give this a try.
Also, one more help , how do I use the source data to create this table. Can this be done with the help of Power Query/DAX.
Or do I need to create them manually and keep updating it once a new user is added.
Thanks,
HN
In Power Query, I would create a new query something like this:
let
Source = Table.SelectColumns(yourTable, "Ops Lead"),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Ops Lead", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), {"OpsLeadTemp1", "OpsLeadTemp2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "OpsLeadList", each {[OpsLeadTemp1], [OpsLeadTemp2]}),
#"Expanded OpsLeadList" = Table.ExpandListColumn(#"Added Custom", "OpsLeadList"),
#"Filtered Rows" = Table.SelectRows(#"Expanded OpsLeadList", each ([OpsLeadList] <> null)),
distinctList = Table.Distinct(Table.SelectColumns(#"Filtered Rows", "OpsLeadList"))
in
distinctList
This will dynamically create a distinct list of all the people featured in your original [Ops Lead] field, using the same technique as my first answer (I knew it would come in useful somehow!).
Also, it looks lke you've accidentally accepted your own answer as the solution on this post, rather than mine.
Pete
Proud to be a Datanaut!
Hi @BA_Pete ,
Thanks for pointing this out.
The new layout is very confusing. Will mark your solution as the correct one.
Thanks
HN
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |