Forum Discussion

B_Rax's avatar
B_Rax
Helper I
1 year ago
Solved

How to Maintain Submission Window Context

I'm currently attempting to create a dax measure that seems simple, but is giving me a lot of trouble. I have a table populated with document submission dates. I want to have a table visual with a Da...
Need Help
  • B_Rax's avatar
    B_Rax
    1 year ago

    I added a blank check field using this DAX:

    Blank Check = 
     DISTINCTCOUNT('Submitted Documents'[Form ID]) & 
     IF(
          ISBLANK(SELECTEDVALUE('Submitted Documents'[Date Submitted])), 
          "No Submission", 
          " Submitted"
     )

    The color formatting DAX is now:

    Submission Window = 
VAR inWindow = 
    IF(
        SELECTEDVALUE('Calendar'[Weekday Num]) >= 1 && 
        SELECTEDVALUE('Calendar'[Weekday Num] ) <= 3, 
        TRUE(), 
        FALSE()
    )
VAR color = 
    SWITCH (
        TRUE(),
        inWindow, "Green",
        "Red"
    )

RETURN
    color

    This leads to this result with some aggregation on Submitter:

    It's starting to look much closer to what I'm trying to achieve. However, if I change "No Submission" to Blank(), which looks much nicer, the color drops again. To fix that I used "" instead of Blank(). I'll try to go with this compromise for now, but I'd love to hear if you or anyone else knows why my conditional formatting disappears when values on the many side of the relationship are included but are all blank:

     

    Thank you!

     

B_Rax's avatar
B_Rax
Helper I
1 year ago

It's possible. I created a new measure that works without checking 'Submitted Documents' for blanks. However, it still does not apply color to blank submission values. 

The new dax:

Submission Window = 
VAR inWindow = 
    IF(
        SELECTEDVALUE('Calendar'[Weekday Num]) >= 1 && 
        SELECTEDVALUE('Calendar'[Weekday Num] ) <= 3, 
        TRUE(), 
        FALSE()
    )
VAR color = 
    SWITCH (
        TRUE(),
        inWindow, "Green",
        "Red"
    )

RETURN
    color

 
New dax with blank check and alternative color if blank:

Submission Window = 
VAR inWindow = 
    IF(
        SELECTEDVALUE('Calendar'[Weekday Num]) >= 1 && 
        SELECTEDVALUE('Calendar'[Weekday Num] ) <= 3, 
        TRUE(), 
        FALSE()
    )
VAR color = 
    SWITCH (
        TRUE(),
        ISBLANK(SELECTEDVALUE('Submitted Documents'[Date Submitted])) = FALSE(), IF(inwindow, 
                "Green", "Red"),
        "Black"
    )

RETURN
    color

 

Both of the above measures have the same result:

 

In the second DAX I'm not sure if the ISBLANK() expression is always evaluating to FALSE() or if the coloring is being ignored all together since no rows are changed to black.

johnt75's avatar
johnt75
Super User
1 year ago

Easiest way to tell would be to create another measure in the visual which returns 

ISBLANK(SELECTEDVALUE('Submitted Documents'[Date Submitted]))
  • B_Rax's avatar
    B_Rax
    Helper I
    1 year ago

    I added a blank check field using this DAX:

    Blank Check = 
     DISTINCTCOUNT('Submitted Documents'[Form ID]) & 
     IF(
          ISBLANK(SELECTEDVALUE('Submitted Documents'[Date Submitted])), 
          "No Submission", 
          " Submitted"
     )

    The color formatting DAX is now:

    Submission Window = 
VAR inWindow = 
    IF(
        SELECTEDVALUE('Calendar'[Weekday Num]) >= 1 && 
        SELECTEDVALUE('Calendar'[Weekday Num] ) <= 3, 
        TRUE(), 
        FALSE()
    )
VAR color = 
    SWITCH (
        TRUE(),
        inWindow, "Green",
        "Red"
    )

RETURN
    color

    This leads to this result with some aggregation on Submitter:

    It's starting to look much closer to what I'm trying to achieve. However, if I change "No Submission" to Blank(), which looks much nicer, the color drops again. To fix that I used "" instead of Blank(). I'll try to go with this compromise for now, but I'd love to hear if you or anyone else knows why my conditional formatting disappears when values on the many side of the relationship are included but are all blank:

     

    Thank you!