show count of IDs that converge based on a second table

[MateusMontemor]

Hello,

I'm facing the current issue, i have the two databases below

The Project has one row per project with its description, financial info and multiple information across the columns, each with one unique id and name,  the SKU by project has one sku per row, having multiple SKUs for each project and SKUs that have multiple projects, (example:

ROW 1: SKU: X, Project Name: One,

ROW2 : SKU Y, Project Name: One

ROW 3: SKU X, Project name: Two,

so thats why the relationship is one to many based on project name

What i wanted is to check sku convergence between project filtering on one project so: 

for the example i gave, if i filter on the slicer Project Name "One", it would show a table with all the projects that have skus in common with project "One" and its info from the Project table:

Slicer Project name: "One" is selected

table:

ROW 1: Project Name "One", Project ID: "1", Investment "$$$"

ROW 2: Project Name "Two", Project ID "2", Investment "$$$"

So basically i would know these 2 project have skus in common and in another table i would show which skus (this one i was able to do)

I tried doing some calculations with filters and crossfilter to try and filter the table but it didn't work, because it seems i always end up in a loop where i'm trying to filter table Project with information from SKU by project and return info from Project table, don't know if it makes sense haha

Thanks in advance for any suggestions!

[Anonymous · Accepted solution]

Hi @MateusMontemor ,

This is a sample data table, and I also added two rows for demonstration.

Solution:

1.Create a separate table with Project Name. Separate means that there is no relationship between your table and the table with SKUs. And the table is created for the slicer.

2.Create a measure for visual-level filter.

Measure = var _sel=SELECTEDVALUE('Table 2'[Project Name])
var _tab=SELECTCOLUMNS(FILTER(ALL('Table'),[Project Name]=_sel),"SKU",[SKU])
return
IF(MAX('Table'[SKU]) IN _tab ,1,0)

Put the measure into the filters and set up show items when the value is 1. The field of the slicer is from Table 2.

As you can see, when "One" is filtered in the slicer, SKU with X and Y are filtered.

You can check more details from my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[amitchandak]

@MateusMontemor , You have to create an independent diconnected table with all projects

Name = distinct(Project[Project Name]) // Do no join with your table

Then you can create a measure like 

measure =

var _sku = summarize(filter(allselected(Project), Project[Project name] in values(Name[Project Name]) ), Project[Sku])
return
countx(filter( Project, Project[Sku] in _sku), Project[Project name])