Summarizing a Matrix visual by a specific field

[anamika_singh]

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

[v-bmanikante · Accepted solution]

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])

            )

    )

RETURN

SUMMARIZE(

    BaseTable,

    YourTable[Name],

    "Has Access",

        VAR _LatestDate = MAXX(FILTER(BaseTable, YourTable[Name] = EARLIER(YourTable[Name])), [Latest Assigned Date])

        RETURN

            CALCULATE(

                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

[anamika_singh]

Thank you 

v-bmanikante

Preview

Thank 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.

[v-bmanikante]

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

[v-bmanikante]

Hi @anamika_singh ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @anamika_singh ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @anamika_singh ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

[Ashish_Mathur]

Hi,

Share the raw data.  Share data in a format that can be pasted in an MS Excel file.

[anamika_singh]

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

[Ashish_Mathur]

What you have shared is the result of a matrix visual, not the raw data from where you created it.

[lbendlin]

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.