Forum Discussion
Summarizing a Matrix visual by a specific field
Hi Team,
Can i get help on the below:
The below is a matrix visual created based on my dataset:
| Name | Has Access | Daily Logins | Profiles Viewed | Response Rate | Assigned Date |
| Abhishek A | Yes | 10 | 100 | 50% | 4/25/2025 |
| Amrita B | Yes | 20 | 150 | 52% | 4/25/2025 |
| Karol | No | 30 | 20 | 50% | |
| Ana P | No | 30 | 200 | 54% | |
| Ana P | Yes | 40 | 300 | 54% | 3/2/2025 |
| Carla | Yes | 50 | 20 | 58% | 4/25/2025 |
| Emily | Yes | 60 | 300 | 53% | 4/10/2024 |
| Emily | No | 70 | 50 | 100% | 2/10/2023 |
Can we convert this to something like this using DAX? Group the above matrix by Name column and pick assigned date as the latest date.
| Recruiter | Has Access | Daily Logins | Profiles Viewed | Response Rate | Assigned Date |
| Abhishek A | Yes | 10 | 100 | 50% | 4/25/2025 |
| Amrita B | Yes | 20 | 150 | 52% | 4/25/2025 |
| Karol | No | 30 | 20 | 50% | |
| Ana P | Yes | 70 | 500 | 54% | 3/2/2025 |
| Carla | Yes | 50 | 20 | 58% | 4/25/2025 |
| Emily | Yes | 130 | 350 | 77% | 4/10/2024 |
Thank you,
Anamika
Hello anamika_singh ,
Thank you for reaching out to Microsoft Fabric Community Forum
Ashish_Mathur lbendlin Thank you for your quick responses.
Please try the below DAX measure to create a calculated table.
finalresult =VAR BaseTable =ADDCOLUMNS(YourTable,"Latest Assigned Date",CALCULATE(MAX(YourTable[Assigned Date]),ALLEXCEPT(YourTable, YourTable[Name])))RETURNSUMMARIZE(BaseTable,YourTable[Name],"Has Access",VAR _LatestDate = MAXX(FILTER(BaseTable, YourTable[Name] = EARLIER(YourTable[Name])), [Latest Assigned Date])RETURNCALCULATE(MAX(YourTable[Has Access]),YourTable[Assigned Date] = _LatestDate),"Daily Logins", SUM(YourTable[Daily Logins]),"Profiles Viewed", SUM(YourTable[Profiles Viewed]),"Response Rate", AVERAGE(YourTable[Response Rate]),"Assigned Date", MAX(YourTable[Assigned Date]))If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
10 Replies
yes, you can do that directly in your table visual (it's not a matrix, unless you use explicit measures?). Set the last column to aggregate to "Latest". And probably the Response Rate to Average rather than sum.
Hi,
Share the raw data. Share data in a format that can be pasted in an MS Excel file.
Hi Ashish,
I think the data I shared can be pasted in excel, please let me know if that's not the case:)
Thanks,
Anamika
What you have shared is the result of a matrix visual, not the raw data from where you created it.
Hello anamika_singh ,
Thank you for reaching out to Microsoft Fabric Community Forum
Ashish_Mathur lbendlin Thank you for your quick responses.
Please try the below DAX measure to create a calculated table.
finalresult =VAR BaseTable =ADDCOLUMNS(YourTable,"Latest Assigned Date",CALCULATE(MAX(YourTable[Assigned Date]),ALLEXCEPT(YourTable, YourTable[Name])))RETURNSUMMARIZE(BaseTable,YourTable[Name],"Has Access",VAR _LatestDate = MAXX(FILTER(BaseTable, YourTable[Name] = EARLIER(YourTable[Name])), [Latest Assigned Date])RETURNCALCULATE(MAX(YourTable[Has Access]),YourTable[Assigned Date] = _LatestDate),"Daily Logins", SUM(YourTable[Daily Logins]),"Profiles Viewed", SUM(YourTable[Profiles Viewed]),"Response Rate", AVERAGE(YourTable[Response Rate]),"Assigned Date", MAX(YourTable[Assigned Date]))If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Thank you
v-bmanikantePreviewThank you so much, your suggestion did help me to create the table. However, the data in the table is an aggregated one. The other requirement i have is that, this table sits within a report which has few slicers and the data in the table should change based on the slicer selections. I was hoping to get something created which was interactive as well. Let me know if that's possible.Hi anamika_singh ,
If you want to filter data using a slicer, that’s absolutely possible.
However, if you're using a column from a newly created table as a slicer (instead of directly from your main table), you'll need to create a relationship between the new table and your original table for the slicer to work properly.Alternatively, you can consider using field parameters, which give you more flexibility to switch between different fields dynamically in your visuals.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
