Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 Mod...
General Comment
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    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 

  • BA_Pete's avatar
    BA_Pete
    5 years ago

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

BA_Pete's avatar
BA_Pete
Super User
5 years ago

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