Forum Discussion
Help with Data Modelling and Slicers
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
Anonymous ,
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
Anonymous ,
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 distinctListThis 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
6 Replies
Hi Anonymous ,
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
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
Anonymous ,
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