Conditional Formatting on a Matrix & More

[tkerr198]

I've been stuck on a problem for my work for a while. I've made progress, but I can't seem to find a prooper solution to my conditional formatting issue.

In Microsoft Power BI, I want to setup a matrix visual to show when the Doc Num of a Contract is New, Open, or Closed using Conditional Formatting. This will be over the course of different Verisons of Data pulls, and separated in different Unit groups.

I want to ensure that I can adjust the DAX so that Closed only appears after a Doc Num disappears (e.g. not showing Closed in the same version it last appears)

My current table has 3 columns:

Doc Num, String

Version, Decimal number

Unit, String

Lastly, I want to apply the conditional formatting to the matrix itself. This is because for each version, the Doc Num may change from New, to Open to Closed. Here's my current code:

ContDocument Status =

VAR _CurrentVersion = SELECTEDVALUE(DataPage_Tables_Append1[Version])

VAR _DocNum = SELECTEDVALUE(DataPage_Tables_Append1[DAC Doc Num])

VAR _CurrentUnit = SELECTEDVALUE(DataPage_Tables_Append1[Unit])

VAR _LastVersion =

CALCULATE(

    MAX(DataPage_Tables_Append1[Version]),

    FILTER(

        ALL(DataPage_Tables_Append1),

        DataPage_Tables_Append1[DAC Doc Num] = _DocNum &&

        DataPage_Tables_Append1[Unit] = _CurrentUnit

    )

)

VAR _FirstVersion =

CALCULATE(

    MIN(DataPage_Tables_Append1[Version]),

    FILTER(

        ALL(DataPage_Tables_Append1),

        DataPage_Tables_Append1[DAC Doc Num] = _DocNum &&

        DataPage_Tables_Append1[Unit] = _CurrentUnit

    )

)

RETURN

SWITCH(

    TRUE(),

    _CurrentVersion = _FirstVersion, "New", _CurrentVersion > _FirstVersion && _CurrentVersion < _LastVersion, "Open", _CurrentVersion = _LastVersion, "Closed", BLANK()

)

This is what the Output currently looks like:

The problem is, that almost all Doc Nums are in Closed status at the last Version (unless the Doc Num is New during that Version), and that is not actually true. Some of  those Doc Nums should be in Open status because it is not confirmed that the Doc Num disappeared.

Another functionality that I want to add to my matrix, is to have a Doc Num be reopened in case there is an edit, and the Doc Num must be revisied. I do not know how to do this.

Any guidance on this issue would be appreciated.

Thank you in advance!

Best,

tkerr98

[v-dineshya]

Hi @tkerr198 ,

Thank you for reaching out to the Microsoft Community Forum.

I have created sample data based on your inputs. Please refer below snap.

Please refer below DAX measure.

Document Status =

VAR _Doc = SELECTEDVALUE(DataPage_Tables_Append1[DocNum])

VAR _Unit = SELECTEDVALUE(DataPage_Tables_Append1[Unit])

VAR _Version = SELECTEDVALUE(DataPage_Tables_Append1[Version])

VAR _VersionsTable =

    CALCULATETABLE(

        VALUES(DataPage_Tables_Append1[Version]),

        ALLEXCEPT(DataPage_Tables_Append1, DataPage_Tables_Append1[DocNum], DataPage_Tables_Append1[Unit])

    )

VAR _FirstVersion = MINX(_VersionsTable, DataPage_Tables_Append1[Version])

VAR _LastVersion = MAXX(_VersionsTable, DataPage_Tables_Append1[Version])

VAR _NextVersion =

    CALCULATE(

        MIN(DataPage_Tables_Append1[Version]),

        ALL(DataPage_Tables_Append1),

        DataPage_Tables_Append1[Version] > _Version

    )

VAR _ExistsNextVersion =

    CALCULATE(

        COUNTROWS(DataPage_Tables_Append1),

        ALL(DataPage_Tables_Append1),

        DataPage_Tables_Append1[DocNum] = _Doc,

        DataPage_Tables_Append1[Unit] = _Unit,

        DataPage_Tables_Append1[Version] = _NextVersion

    )

VAR _PrevVersion =

    CALCULATE(

        MAX(DataPage_Tables_Append1[Version]),

        ALL(DataPage_Tables_Append1),

        DataPage_Tables_Append1[Version] < _Version

    )

VAR _ExistsPrevVersion =

    CALCULATE(

        COUNTROWS(DataPage_Tables_Append1),

        ALL(DataPage_Tables_Append1),

        DataPage_Tables_Append1[DocNum] = _Doc,

        DataPage_Tables_Append1[Unit] = _Unit,

        DataPage_Tables_Append1[Version] = _PrevVersion

    )

VAR _WasClosedBefore =

        _ExistsPrevVersion = 0 && _Version > _FirstVersion

RETURN

SWITCH(

    TRUE(),

    _Version = _FirstVersion, "New",

    _WasClosedBefore, "Reopened",

    _ExistsNextVersion > 0, "Open",

    _ExistsNextVersion = 0, "Closed",

    BLANK()

)

Please refer below conditional formatting measure.

Document Status Color =
SWITCH(
[Document Status],
"New", "#4CAF50",
"Open", "#C0A000",
"Closed", "#C00000",
"Reopened", "#673ab7"
)

Place the above measure in below path.

In Format --> Cell elements --> Background color --> Format by: Field value

Please refer below output snap  and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @tkerr198 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[tkerr198]

Hello,

Thank you for your repsonse! I tried this solution out, but I am somehow running into problems with it.

Reopened is appearing randomly in my matrix, when the DocNum has not been closed between Versions. Also, almost all versions at the end of the matrix still show as Closed.

Is there something that I am possibly missing?

[v-dineshya]

Hi @tkerr198 ,

Please refer below steps.

1. Created Disconnected table.

VersionTable =
ADDCOLUMNS (
GENERATESERIES(
MIN(DataPage_Tables_Append1[Version]),
MAX(DataPage_Tables_Append1[Version]),
1
),
"Version", [Value]
)

2.  Please refer below , updated DAX measures.

VAR _NextVersion =
CALCULATE (
MIN ( VersionTable[Version] ),
VersionTable[Version] > _Version
)

VAR _PrevVersion =
CALCULATE (
MAX ( VersionTable[Version] ),
VersionTable[Version] < _Version
)

3.  In Matrix visual,  Rows --> DocNum,  Columns --> VersionTable[Version] and 

Values --> Document Status.

Please refer below output snaps. and attached PBIX file.

If you still facing the same issue. Please provide sample pbix file and provide sample output snap, and please provide more information regarding your query, it will help us to replicate the scenario. Do not include sensitive information. 

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @tkerr198 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Zanqueta]

Hello @tkerr198

Look at you code our current logic marks almost all Doc Nums as Closed in the last version because it assumes that the last recorded version for that Doc Num means closure. However, this is not necessarily true unless the Doc Num disappears in the next version. To fix this and add the “Reopened” functionality, you need to check whether the Doc Num exists in the next version and whether it reappears after disappearing. 

Please try it:

ContDocument Status =
VAR _CurrentVersion = SELECTEDVALUE(DataPage_Tables_Append1[Version])
VAR _DocNum = SELECTEDVALUE(DataPage_Tables_Append1[DAC Doc Num])
VAR _CurrentUnit = SELECTEDVALUE(DataPage_Tables_Append1[Unit])

VAR _FirstVersion =
CALCULATE(
    MIN(DataPage_Tables_Append1[Version]),
    FILTER(ALL(DataPage_Tables_Append1),
        DataPage_Tables_Append1[DAC Doc Num] = _DocNum &&
        DataPage_Tables_Append1[Unit] = _CurrentUnit
    )
)

VAR _NextVersionExists =
CALCULATE(
    COUNTROWS(DataPage_Tables_Append1),
    FILTER(ALL(DataPage_Tables_Append1),
        DataPage_Tables_Append1[DAC Doc Num] = _DocNum &&
        DataPage_Tables_Append1[Unit] = _CurrentUnit &&
        DataPage_Tables_Append1[Version] > _CurrentVersion
    )
)

VAR _PreviousVersionExists =
CALCULATE(
    COUNTROWS(DataPage_Tables_Append1),
    FILTER(ALL(DataPage_Tables_Append1),
        DataPage_Tables_Append1[DAC Doc Num] = _DocNum &&
        DataPage_Tables_Append1[Unit] = _CurrentUnit &&
        DataPage_Tables_Append1[Version] < _CurrentVersion
    )
)

RETURN
SWITCH(
    TRUE(),
    _CurrentVersion = _FirstVersion, "New",
    _PreviousVersionExists > 0 && _NextVersionExists > 0, "Open",
    _PreviousVersionExists > 0 && _NextVersionExists = 0, "Closed",
    BLANK()
)

If a Doc Num disappears and then reappears, you can add:

_PreviousVersionExists > 0 && _NextVersionExists > 0 && _CurrentVersion <> _FirstVersion, "Reopened"

If this answer was helpful in any way, I would be pleased to receive a 👍, as well as the satisfaction of seeing a DAX measure work for the first time without needing yet another FILTER.
Please mark it as the accepted solution. This helps other community members find the quickest path and saves them from another endless loop 🌀.