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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • BA_Pete's avatar
      BA_Pete
      Super User
      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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