User Profile
tkerr198
Frequent Visitor
Joined 8 months ago
User Widgets
Contributions
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 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
