DAX help filtering a table visual with an IN list form a calculated table.

[924pbi]

I have a CatsColars table from which I have created a calulated table visual that groups cats and collars to show how many cats have the same number of collars. e.g. 2 cats have 4 colars, 1 cat has 3 colars,etc.

By clicking on the number of colars in the colar column of the calculated table visual, i want to filter the CatsColars table to only show those cats with the clicked on number of colars.

e.g. if i click on the "2" in the colars column of the calculated table visual the measure against the CatsColars table should show a 1 for the 4 rows belonging to the two cats with 2 colars;  Link to pbix file  
How do i get the measure to correctly return the 1 and 0 so that i can filter the visual? thanks in advance.

[924pbi]

@talespin awesome, now i also understand how you have maintained lineage. thanks heap

[talespin]

You're welcome.

[924pbi]

Hi @talespin, thanks for reviewing the problem. Is there an alternative way to get a simiar outcome, if not that way? Also not sure i understand completely given i am passing values that do the fitlering and that the count of rows effected is returned correctly. Also, isn't lineage about both tables being derived from the same physical source table, which it is, and the same data type is passed becuase it comes from the same table?

[talespin · Accepted solution]

hi @924pbi ,

Create a new table with colar count for each Cat and Use Cat name in this new table to filter main table.

-Keep original table CatColars

-Create new table and join this table to CatColars on column [Cat] only in Datamodel.

Table =

ADDCOLUMNS(

                            SUMMARIZE(

                                        CatColars,

                                        CatColars[Cat]

                            ),

                            "ColarCount",

                            VAR _Cat = CatColars[Cat]

                            RETURN COUNTX( FILTER(CatColars, CatColars[Cat] = _Cat), CatColars[Colar])

                )

-Create a measure

Meow = COUNTX( 'Table', 'Table'[Cat])

Let me know if any questions.

[talespin]

Hi @924pbi ,

I do not think it will work, Your calculated table can't filter the actual table.

I think its due to Data Lineage, once Data Lineage is broken you can't filter one table with another. Your Main table is text values and Calculated table has numbers(Its not just about data type) but Data Lineage as well.