topic Returns a summary table over the most common values between categories in DAX Commands and Tips

Returns a summary table over the most common values between categories

THPNO80 — Wed, 16 Nov 2022 19:23:35 GMT

Hi there!

I´m trying to get a summary table over the most accessed reports between two or more categories (by function) in a "Log Events" table. The results will be helpful to create a cleaner network analysis chart. See the example below:

User	Function	Report	Log_Events
A	Marketing	Report_A	5
A	Marketing	Report_B	6
A	Marketing	Report_C	2
B	Account	Report_D	1
B	Account	Report_A	8
C	CS	Report_B	9
D	Finance	Report_B	10
D	Finance	Report_A	2
E	R&D	Report_C	3

Expected outcome:

Report	Functions	Log_Events
Report_A	3	15
Report_B	3	25
Report_C	2	5
Report_D	1	1

I hope you can help me... thanks in advance!

Re: Returns a summary table over the most common values between categories

serpiva64 — Wed, 16 Nov 2022 21:30:27 GMT

Hi,

try

If this post is useful to help you to solve your issue consider giving the post a thumbs up

and accepting it as a solution !

Re: Returns a summary table over the most common values between categories

tackytechtom — Wed, 16 Nov 2022 21:39:30 GMT

Hi THPNO80 ,

How about this:

Here the code for creating a new table in DAX:

DerivedDaxTable = 
SUMMARIZE (
    'Table',
    'Table'[Report],
    "Functions",  COUNT ( 'Table'[Function] ), 
    "Log_Events", SUM ( 'Table'[Log_Events] )
)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Returns a summary table over the most common values between categories

THPNO80 — Thu, 17 Nov 2022 19:01:07 GMT

Hi tackytechtom I really appreciate your help, it worked! but when applied to the model it didn't get the expected result. I'm missing something else 🤔 Thanks anyway 👍

Re: Returns a summary table over the most common values between categories

THPNO80 — Thu, 17 Nov 2022 19:04:43 GMT

Hello serpiva64 I really appreciate your help too, but, as I commented above, I didn't get the expected result in the model... Regards! 👍