Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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.
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.
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.
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.
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.
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.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
90 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |