Expand/Collapse row in table/matrix to group milestones, conditional formatting icon for main row

[Anonymous]

Hello,

This is probably a really dumb question and might just be a table formatting issue, but can't think right now so hoping someone could help out. 

I am trying to create a Power BI table or matrix (whichever works) where the main item is displayed with an expandable + sign on the side that will subsequently display all the milestones underneath. 

Currently I'm pulling data from an Excel table that just has the milestones underneath the main item as individual rows. See screenshot below:

The matrix I have in Power BI currently looks like this:

I'm trying to have it so that the main items are displayed as the bolded, expandable rows, and have all the associated milestones listed underneath it (perhaps in a numbered list format) within the collapsible row. As well, for that main row I want the other columns (dates, % complete, status) to display what's in the Excel sheet for that main item, instead of displaying a sum or average of all the milestones under that row (which is what Power BI usually does for expandable rows). 

I would also like the status to only display the symbol and not the number, but couldn't find a way to do that under conditional formatting. 

Thanks a lot!

[Anonymous]

Don't know why this screenshot doesn't get included in my original post but here's the Excel sheet layout

[v-lid-msft]

Hi @Anonymous ,

We can try to add a calculated column and use following measure to meet your requiremnet:

Calculated Column:

MainItemNumber = if(FIND(".",[No]&"",,-1)>0,LEFT([No],FIND(".",[No]&"",,-1)-1),BLANK())

Measures:

Measure_Start_Date = 
IF (
    ISINSCOPE ( 'Table'[Item Description] ),
    MIN ( 'Table'[Start Date] ),
    IF (
        ISINSCOPE ( 'Table'[No] ),
        BLANK (),
        CALCULATE(MIN('Table'[Start Date]),FILTER(CALCULATETABLE(ALLSELECTED('Table'),ALL('Table'[MainItemNumber])),'Table'[No]&"" in FILTERS('Table'[MainItemNumber])))
    )
)

Measure_Due_Date = 
IF (
    ISINSCOPE ( 'Table'[Item Description] ),
    MIN ( 'Table'[Due Date] ),
    IF (
        ISINSCOPE ( 'Table'[No] ),
        BLANK (),
        CALCULATE(MIN('Table'[Due Date]),FILTER(CALCULATETABLE(ALLSELECTED('Table'),ALL('Table'[MainItemNumber])),'Table'[No]&"" in FILTERS('Table'[MainItemNumber])))
    )
)

Measure_%_PROJECT = 
IF (
    ISINSCOPE ( 'Table'[Item Description] ),
    sum ( 'Table'[% OF PROJECT] ),
    IF (
        ISINSCOPE ( 'Table'[No] ),
        BLANK (),
        CALCULATE(SUM('Table'[% OF PROJECT]),FILTER(CALCULATETABLE(ALLSELECTED('Table'),ALL('Table'[MainItemNumber])),'Table'[No]&"" in FILTERS('Table'[MainItemNumber])))
    )
)

Measure_%_Complete = 
IF (
    ISINSCOPE ( 'Table'[Item Description] ),
    sum ( 'Table'[% COMPLETE] ),
    IF (
        ISINSCOPE ( 'Table'[No] ),
        BLANK (),
        CALCULATE(sum('Table'[% COMPLETE]),FILTER(CALCULATETABLE(ALLSELECTED('Table'),ALL('Table'[MainItemNumber])),'Table'[No]&"" in FILTERS('Table'[MainItemNumber])))
    )
)

Measure_Status = 
IF (
    ISINSCOPE ( 'Table'[Item Description] ),
    SUM('Table'[Status]),
    IF (
        ISINSCOPE ( 'Table'[No] ),
        BLANK (),
        CALCULATE(SUM('Table'[Status]),FILTER(CALCULATETABLE(ALLSELECTED('Table'),ALL('Table'[MainItemNumber])),'Table'[No]&"" in FILTERS('Table'[MainItemNumber])))
    )
)

But due to the limitation of condition format, we cannot apply the icon and data bar on the main item

Best regards,

[Anonymous]

Hi @v-lid-msft 

Thank you for your reply! I tried to implement your measures into the report, and now it looks like this:

I was wondering if there's a way to have 'Test Item 1' displayed instead of the '1' as the main item?

Also, if I remove the [No.] column from the Rows field (see below) it looks fine as well, just need 'Test Item 1' show up instead of the '1'. 

Thanks again!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use another calculated column as row field to meet your requiremnet:

MainItemDescription = if(FIND(".",[No]&"",,-1)>0,CALCULATE(MAX('Table'[Item Description]),FILTER('Table','Table'[No]&"" = LEFT(EARLIER([No]),FIND(".",EARLIER([No])&"",,-1)-1))),BLANK())

Then we replace all the 

'Table'[No]&"" in Filters('Table'[MainItemNumber])

to 

'Table'[No]&"" in DISTINCT('Table'[MainItemNumber])

Best regards,

[djduangjai]

I have 3 levels (hierarchy) of data, how can I apply the code?