Forum Discussion
Matrix Columns, how to filter by only one
I have two columns in my matrix like the view below. It is base on project ID and then they are drill down by date completed and date expected to be completed
I have create a column filter that filters on blanks only and blank on project ID 700. but i get mix results.
| ID | 500 | 700 | ||
| Date Completed | Date Expected | Date Completed | Date Expected | |
| Blue Inc. | 4/1/2020 | 4/1/2020 | 5/1/2020 | |
| Orange Company | 4/1/2020 | 4/1/2020 | 8/1/2020 | 8/1/2020 |
| Purple Company | 05/8/2020 | 3/25/2020 | 4/25/2020 | 3/24/2020 |
This is what i get when i use the column filter that filters on blanks only. It shows the rows i need but it removes the dates from Project ID 500.
| ID | 500 | 700 | ||
| Date Completed | Date Expected | Date Completed | Date Expected | |
| Blue Inc. | 5/1/2020 |
If create a filter by Project ID 700 and blanks, The column for Project ID 500 get remove and i need to keep it.
| ID | 700 | |
| Date Completed | Date Expected | |
| Blue Inc. | 5/1/2020 |
This is the output i am expecting to get, only filter by 700
| ID | 500 | 700 | ||
| Date Completed | Date Expected | Date Completed | Date Expected | |
| Blue Inc. | 4/1/2020 | 4/1/2020 | 5/1/2020 |
Any help will be greately appreciated.
5 Replies
- amitchandak
Super User
Anonymous , Not very clear.
Create a filter column like this
Switch (True(),
isblank([ID]) , "Blanks",
"Non Blanks")or a mesure like
measure =
if (selectedvalue(Table[ID]) = 700 , calculate(countrows(Table),filter(all(Table), not(isblank(Table[ID])))),calculate(countrows(Table)))I am attaching a sample of the pbix file. maybe this will make sense the swich statement does the same thing as the If statement i had already.
Thanks Evelyn,
This seems like a temporary work around, because i can't have the Total and the Filter showing on my visual. I'll have to hide them by changing the fonts and background, i'll have some column with borders that have no values if i I just make the filter and subtotal blend in to visual.
Also, I applied same filter to my none sample data and it just showed all the data regardless of any visual or page filters. That be something else I'll have to look into cause in the sample data is not doing that. Maybe because the sample data is small.
My ideal view would be to show the data in this matter without having to add a column subtotal or filter within the values.ID 500 700 Date Completed Date Expected Date Completed Date Expected Blue Inc. 4/1/2020 4/1/2020 5/1/2020 thanks,
