I can't get a matrix to do a logic test when the value is blank

[JoeBI]

I'm creating a matrix that shows when Status Reports for projects were submitted or were not submitted.  If a report was submitted it should show a green dot, if a report was not submitted it should show a red dot. If however no previous reports have ever been submitted for that project it should show a grey dot.  ie it doesn't make sense to say a report wasn't submitted last month for a project that only just started.  While I can get DAX to return a value if a report was submitted, I can't get it to return different values if one was not submitted.  

Here is what I can get it to show.  I have conditional formatting icons set to 0 = grey, 1 = red. 2 = green. The values at the tops of the columns are report submission dates.

Here is how I want it to look (edited in paint) so you get the objective :

Here is the DAX Code I am using for "Report Submitted" which I want to return 2 if a report was submitted, 0 if a report was not submitted but the report date is less than the first report submitted date, and 1 if a report was not submitted but is greater than the first report submitted date. 

Report Submitted = 
VAR first_report_date = 
    CALCULATE(
        FIRSTDATE('Status'[Report Date]), 
        FILTER(
            'Status', 
            'Status'[Project Number] = SELECTEDVALUE('Status'[Project Number])
        )
    )

RETURN 
    IF(
        SELECTEDVALUE('Status'[Report Date]) = BLANK(),
        IF(
            SELECTEDVALUE('Status'[Report Date]) < first_report_date, 
            0, 
            1
        ),
        2
    )

 I know this doesn't work, and its silly to be asking for a logic test when the IF statement only enters if that value is blank, but I've tried many things and can't crack this so asking for help.

I can confirm the function for first_report_date does work because ive tested that in another table. 

Any ideas?

[Anonymous]

Hi @JoeBI ,

Indeed, I did try that for a long time. You need a date table to retrieve the period you need to return the status in advance, and then use the IF statement to make a judgment.

Best regards,
Community Support Team_ Scott Chang

[JoeBI]

Thanks Scott, I did inititally try to introduce a date table to solve another problem - sorting the dates at the top of the matrix in reverse order, but it didn't work.  I will try it again and see if it can solve this problem as well.  Stay Tuned.

[JoeBI]

Hi @some_bih , that doesn't work. It still returns blanks on all entries where there is no report. 

[some_bih]

Please share file

[JoeBI]

Hey @some_bih  here is the file shared on google drive.  I turned off conditional formatting, if we can get the matrix to show all 0's, 1's and 2's then thats the  hard part done.  0 = No previous reports ever submitted, 1 = previous reports have been submitted but the one for the selected date has not been submitted, and 2 = report submmitted.  Thanks for your help.

https://drive.google.com/file/d/10I5Yzdq_we9Hhts6pbVFB2ew1JRkkkkZ/view?usp=sharing

[some_bih]

Hi @JoeBI I could not even try to access today or next two days as I will be out of office today and tomorrow is weekend, but for sure I will take look late Sunday or Monday.

[some_bih]

Hi @JoeBI I am looking your file and model

Questions: 

- Did you already in file applied conditional formatting or you will do it? I could not see green / red status.

- There is no Date / Calendar table in pbi - why not?

- column 'Status'[Report Date] is key and only column for figure out "status" for report? 

- concerning table Status and matrix from your example. As you can see from DAX query below for column Project Name  "Mimecast Replacement" or "Large File Send Replacement" there is only 1 row for these projects, but there are different colored in your expected visual

Could you explain reasons why?

[JoeBI]

Hi @some_bih 

- AsI said in my post, I turned conditional formatting off.  If you can get the matrix to show 0's, 1's and 2's then the hard part is done.  Conditional formatting is an easy 1 minute job at the end which either of us could do.

- correct there is no date table, there has been no need to have one.   The 'Report Date' is a variable in the 'Status' table in the 'Report Date' column.  When users submit status reports (on excel templates that get ingested by PBI) the report date is included on that.

- The key for the 'Status' table is not 'Report Date', it is 'Project Number & Date'

- Every status report submited by a user takes up one row of the 'Status' table. Both of the projects  'Mimecast Replacement' and 'Large File Send Replacement' have only submitted one status report on 22/8/24  but when other projects submitted a status report on 5/9/24 they did not submit one and they should have.  Thats why I want these to show up as red.

Hope that clears up your questions, let me know if you have any others.

Thanks for your assistance.

[some_bih]

Hi @JoeBI I did something, check tab Report_2 on enclosed file

I could not find proper solution for grey case so please check it and play with combination with Background color, cell ements. The issue for grey is zero (blank) handling in power bi.

[JoeBI]

Hi @some_bih, I've had a look.   Unofortunately the needle has not moved at all, the problem is still exactly where I left it - all blank values are treated the same, but I very much appreciate you trying to help. 

[some_bih]

Hi @JoeBI 

ok. I would like to point that when you want to use / leverage date columns almost anyhow in Power BI file should have Date / Calendar table conected via relationship with some other column. When there is relationship you can use it and leverage it more using measure etc.

[JoeBI]

Hey. I'm just away for two days I'll send it through on Thursday. 

[some_bih]

Hi @JoeBI it is not according to best practice try to put +0 in your measure definition, like

RETURN 
    IF(
        SELECTEDVALUE('Status'[Report Date]) = BLANK(),
        IF(
            SELECTEDVALUE('Status'[Report Date]) < first_report_date, 
            0, 
            1
        ),
        2
    )+0