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!

     

johnt75's avatar
johnt75
Super User
1 year ago

I think the problem is because the measure will return a non-blank value regardless of anything in the submitted documents table, so all possible combinations return something.

Try

Submission Window =
IF (
    NOT ISEMPTY ( VALUES ( 'Submitted Documents'[Date Submitted] ) ),
    IF (
        SELECTEDVALUE ( 'Calendar'[Weekday Num] ) >= 1
            && SELECTEDVALUE ( 'Calendar'[Weekday Num] ) <= 3,
        "On Time",
        "Late"
    )
)
B_Rax's avatar
B_Rax
Helper I
1 year ago

This may be on the right track, but it does not fully solve the issue when I try to apply this to color formatting. For example, if I use my original dax as color formatting I can easily see the submission windows. With the current fix we lose color formatting on any date that didn't have a submission:

 



  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    Not too familiar with the conditional formatting side of things. Is there an option to give it a colour if the value is blank ?

    • 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]))