Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harshnathani
Community Champion
Community Champion

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

 

1.JPG

 

 

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

 

 

 

2 ACCEPTED SOLUTIONS

@harshnathani ,

 

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 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

@harshnathani ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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

@harshnathani ,

 

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 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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

@harshnathani ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.