Forum Discussion

tkerr198's avatar
tkerr198
Frequent Visitor
8 months ago
Solved

Conditional Formatting on a Matrix & More

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 ...
DAX
Dax Help
Need Help
need Help with measure
Report Server
  • v-dineshya's avatar
    v-dineshya
    8 months ago

    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

     

     

Zanqueta's avatar
Zanqueta
Super User
8 months ago

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 🌀.