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 Date column and each cell will be colored based on being in or out of the submission window defined in the dax measure. I have a dax measure that works correctly when only the Date column is present in the table visual. However, if I add values from the submissions table (such as submission date or submitter name) it breaks down. Here are some screenshots of an example PBIX I created to isolate the issue:

The leftmost visual consists of the 'Calendar'[Date] column and [Submission Window] measure. 
The middle visual shows 'Calendar'[Date] with 'Submissions'[Submitter] and [Form ID]. This behaves as expected.
The rightmost visual shows values from 'Calendar' and 'Submissions' with theh [Submission Window] measure added. It seems the relationship between 'Calendar' and 'Submissions' has broke down and all values are pairing.


Here is the data model:

Date and Date Submitted are both Date data type. 'Calendar' is set as the date table using [Date].

This is the dax expression used (this is one of many iterations):

Submission Window = IF(SELECTEDVALUE('Calendar'[Weekday Num]) >= 1 && SELECTEDVALUE('Calendar'[Weekday Num]) <= 3, "On Time", "Late")
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!

     

6 Replies

  • 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 ?