topic DAX help filtering a table visual with an IN list form a calculated table. in DAX Commands and Tips

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

924pbi — Thu, 25 Jan 2024 01:21:49 GMT

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.

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

talespin — Thu, 25 Jan 2024 05:51:14 GMT

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.

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

924pbi — Thu, 25 Jan 2024 06:09:18 GMT

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?

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

talespin — Thu, 25 Jan 2024 09:38:02 GMT

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.

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

924pbi — Thu, 25 Jan 2024 22:41:29 GMT

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

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

talespin — Sun, 28 Jan 2024 06:09:46 GMT

You're welcome.