How to create different filters for each column in a table visualization?

[mandynguyen]

Hi, I have an example of table visualization as follows:

AREA     DOG     CAT     BIRD

Home        2         4           1

School       6         8          10

Zoo           22       30         50

In my database, each of the animal has its own column. In the table visualization, I add each of them in the "values" field.

Now, I would like to, in the same table, filter each column differently (different date for instance). How can I do that?

[v-yalanwu-msft · Accepted solution]

Hi, @mandynguyen ;

You could create a measure.

Measure = CALCULATE(COUNT('Table'[State]),FILTER('Table',[Date]<=MAX('Date'[Date])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @mandynguyen ;

You could create a measure.

Measure = CALCULATE(COUNT('Table'[State]),FILTER('Table',[Date]<=MAX('Date'[Date])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mandynguyen · Accepted solution]

Thanks. This seems to work too.

I found another solution is that in the new calculated measure, I set "userelationship" between the dates in the main table with the date in calendar table. Then after that, I can choose AS OF date from calendar table and each of the column get filtered independently.

[v-yalanwu-msft]

Hi, @mandynguyen ;

If date A filters Dog,2,6 lines; How are the other columns displayed?
Can you share some scenes to make me understand the results you want output to show? Maybe measure can do that. Let me understand your logic and look forward to your reply.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mandynguyen]

Hi, thanks for asking for more information!

I'll use this real data matrix above for clarification.

In my data set, every row is a student from different campus type. Each student may have all dates of Submit, Admit, Confirm, Enroll or a few within the 4 options.

Now in this matrix, I want to have counts of the 4 columns above AS OF a specific date I choose.

For example, if I choose want all records since beginning to today-a-year-ago (6/22/2021), the matrix will be filtered ONLY records that have Submit, Admit, Confirm, Enroll dates before 6/22/2021.

While in reality, in column Submit, I want them to count all records that have submit before 6/22/2021 regardless of their Admit, Confirm, and Enroll are before or after 6/22/2021. I also want them to count the other columns independently from others as well.

Right now I'm thinking to find a way to have each of the 4 columns to obliged to different filters to achieve that. But if you have any suggestions to get the same targeted results, I would love to learn about it.

[Ashish_Mathur]

Hi,

In the Query Editor, you should select all columns other then the Submit, Admit, Enroll and Confirm columns, right click and select "Unpivot Other Columns".  Rename the Value column to Date.  Create a Calendar Table and a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Build the slicer from the Date column of the Calendar Table.  Write this meausre

Measure = sum(Data[Amount])

Hope this helps.