topic Re: Matrix aggregation and filtering in DAX Commands and Tips

Matrix aggregation and filtering

Anonymous — Thu, 15 Sep 2022 10:27:30 GMT

Hi,

I need to group values in the matrix by Source and Destination columns. How can I achive this? Thanks for any advice

Previous Segment = Source

Currenct Segment = Destination

"Calendar table" is not connected with "Table"

Re: Matrix aggregation and filtering

tamerj1 — Thu, 15 Sep 2022 12:48:00 GMT

Anonymous

Please try

Source =
VAR SelectedDate =
MIN ( 'Calendar'[Date] )
VAR CurrentIDTable =
FILTER ( 'Table', 'Table'[segment date] > SelectedDate )
VAR FirstRecord =
TOPN ( 1, CurrentIDTable, 'Table'[segment date], ASC )
RETURN
MAXX ( FirstRecord, 'Table'[segment_name] )

Re: Matrix aggregation and filtering

Anonymous — Thu, 15 Sep 2022 13:07:07 GMT

tamerj1

thanks, this works but the problem is when I remove person_id from the table this happens ( it is grouping all the fields in the table) and I want to achive this on first screentshot in the first post

Re: Matrix aggregation and filtering

tamerj1 — Thu, 15 Sep 2022 13:12:22 GMT

Anonymous

In the first screenshot, does each row represents on Id?

Re: Matrix aggregation and filtering

Anonymous — Thu, 15 Sep 2022 13:30:35 GMT

tamerj1 No, this is grouped by source and destination and Total Users = count(persona_id)

this is not easy to implement because slicer can't affect on calculated table so I've created this two measures - source and destination. I am not sure how to show only these two measures on matrix/table

Re: Matrix aggregation and filtering

tamerj1 — Thu, 15 Sep 2022 13:53:29 GMT

Anonymous
Technically it is not possible as power bi does not support slicing by or grouping by measures. There must be a column. Actually it's not clear to me what does it mean to group by a measure if it has any meaning.

Re: Matrix aggregation and filtering

Anonymous — Thu, 15 Sep 2022 14:02:40 GMT

yes, ok I should explain it better . I am not sure if this is technically achivable but according to date range on slicer I want to take first (soruce) and last (destination) segment_name for each persona_identifier and then group this by source and destination with count on persona_identifier

Re: Matrix aggregation and filtering

tamerj1 — Thu, 15 Sep 2022 14:22:32 GMT

Anonymous
I can think of two possibilities. We have a good chance to do that by concatenation and a smaller chance by filter measure. Please share a sample data to work with and let me see if I can produce somthing.

Thank you

Re: Matrix aggregation and filtering

Anonymous — Fri, 16 Sep 2022 09:29:20 GMT

Hi tamerj1

sorry for late reply. here is a sample dataset

Re: Matrix aggregation and filtering

tamerj1 — Fri, 16 Sep 2022 09:33:43 GMT

Anonymous

I don't see any link?

Re: Matrix aggregation and filtering

Anonymous — Fri, 16 Sep 2022 11:07:34 GMT

Hi tamerj1

yes, sorry. here is the link https://drive.google.com/file/d/1Bx18wxlMb-JFrtZIE-l-BU3OdCtdvkAg/view?usp=sharing