Matrix visual - Show Count of event when aggregated

[malpani]

Hello Experts, 

I am trying to create a calendar view using Matrix visual to show all product milestones event. 
I have two fields on rows - Product Name and Release Name. When Product name is expanded it shows each Release Name in separate row.
On columns I have added Year, Month and Day.
In values, I have a measure 'Event'. Event measure is given in attached sample PBIX file - https://drive.google.com/file/d/1C7rtWnvx1kiJsCbvW0f5Y_jtdmxEX-r_/view?usp=sharing

This view is fine when rows are expanded.

But when I collapse rows it consolidates and shows all values from indiviual rows.
Current output when rows are collapsed - 

Expected output - 
When rows are collapsed, I would like to show only count of Milestones as shown below - 

It would be even better if it always shows such count of Milestone when rows are collapsed/expanded.

I tried but I could not manage to modify 'Event' measure to have different behaviour when rows are collapsed or expanded.

Regards

[Daniel29195 · Accepted solution]

@malpani 

usethe isinscope()  function.  

isinscope functions lets you control the calculation you want to achieve base on the level of hiearrchy of a matrix.

example : 

measure = 

switch(

true() , 

isinscope( tabl_name[col_name_1] , [ measure 1 ] , 

isinscope( tabl_name[col_name_2] , [ measure 2 ] , 
isinscope( tabl_name[col_name_3] , [ measure 3] , 

....

) 

hope this helps .

NB: always start your switch(   isinscope() with the lowest level 

example if yuor matrix is : 

category -->  subcategory -->  product

start : 

switch(

true() , 

isinscope( product[product_name] , [ measure 1 ] , 

isinscope( subcategory[subcategory_col_name] , [ measure 2 ] , 
isinscope( category[categroy_col_name] , [ measure 3] , 

....

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Daniel29195 · Accepted solution]

@malpani 

usethe isinscope()  function.  

isinscope functions lets you control the calculation you want to achieve base on the level of hiearrchy of a matrix.

example : 

measure = 

switch(

true() , 

isinscope( tabl_name[col_name_1] , [ measure 1 ] , 

isinscope( tabl_name[col_name_2] , [ measure 2 ] , 
isinscope( tabl_name[col_name_3] , [ measure 3] , 

....

) 

hope this helps .

NB: always start your switch(   isinscope() with the lowest level 

example if yuor matrix is : 

category -->  subcategory -->  product

start : 

switch(

true() , 

isinscope( product[product_name] , [ measure 1 ] , 

isinscope( subcategory[subcategory_col_name] , [ measure 2 ] , 
isinscope( category[categroy_col_name] , [ measure 3] , 

....

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[malpani]

Hi @Daniel29195,

Many thanks for the guidance. It worked for me.
I have one more question on the measure I have - 
I want to show date part of this measure in different color. Is it possible to add a color ?

Events =

VAR EventList =

    CONCATENATEX (

        FILTER (

            'Excel_Manual',

            NOT ISBLANK ( 'Excel_Manual'[Milestone] ) && NOT ISBLANK ( 'Excel_Manual'[DESIGN ELEMENT DESC_Format] )

        ),

        IF (

            'Excel_Manual'[Index] > 1,

            UNICHAR(10),

            ""

        ) &

        FORMAT ( 'Excel_Manual'[Date_Roadmap], "d MMM" ) & UNICHAR(10) &

        'Excel_Manual'[Milestone] & " " & 'Excel_Manual'[DESIGN ELEMENT DESC_Format],

        UNICHAR(10)

    )

RETURN

    IF (

        NOT ISBLANK ( EventList ),

        EventList,

        BLANK ()

    )

[Daniel29195 · Accepted solution]

@malpani 

you can create a measure like this : 

measure = 

switch(

true() , 

condition , " color_in_hexa_ or _ color name "   --  [ ex :  #ffffff or  white ]

condition2 ,   " color_in_hexa_2 or _ color name_2  "

)

then you use this measure in the conditional formatting : 

you choose field value , then you select the measure you have created . 

hope this makes sense. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[malpani]

@Daniel29195: 
In the measure 'Event' it has two fields - Date and Design Element Format concatenated. 
I cannot  separate them else it will display first all Dates and then Design Element. 
I want to show them concatenated with each date. 
But to make it more readable, I want to highlight Date in different color.
Currently it is shown like this 

[Daniel29195]

@malpani 

you want to highlight only the date in the cell . right ? 

this isnt doable as far as i know .

you can change the the font color of the whole cell text .

maybe to highlight the date in the cell,  you can add another unichar(10) . to increment the space between the date and the data in the same cell .