'Show items with no data' - table showing incorrect values, how to amend blank values??

[ansa_naz]

Hi all

Hopefully someone can help with below, as I cant figure out how I can do this

I have three tables: Customers, Lifts and Reports. They are joined by a one to many relationship. I need to return the most recent report date with its Overdue value, for each lift. I have selected to return items with no data on this table. I have a slicer on the customer table to exclude Customer F

Issues - I need to replace blank values with a text value, the data being returned is incorrect, and the slicer on the Customer table is not being honoured

For the Report Date, I have selected to return 'Latest' value. For the Overdue column, I have selected to return the Last value:

Lifts table:

LiftID      Customer         LiftRef

1             A                        Lift1

2             A                       Lift2

3             B                        Lift3

4             C                        Lift4

5             D                       Lift5

6             E                        Lift6

7             E                        Lift7

8             F                        Lift8

9             F                        Lift9

Customer table:

Customer

A

B

C

D

E

F

Reports table:

LiftID        Report Date              Overdue

1              01/01/19                   Yes

1              15/06/18                   No

1              12/04/18                   No

2              15/09/18                   Yes

2              08/12/18                  No

4              01/01/19                  Yes

6              01/02/19                  No

I have a slicer on the Customer column, to exclude customer F, and I would like to return the following:

LiftID            LiftRef           Latest Report Date                Overdue

1                   Lift1              01/01/19                               Yes

2                   Lift2             08/12/18                                No

3                   Lift3             No Report                              No Report

4                   Lift4             01/01/19                                Yes

5                   Lift5             No Report                              No Report

6                   Lift6             01/02/19                                No

7                   Lift7            No Report                               No Report

What I am actually getting is:

LiftID            LiftRef           Latest Report Date                Overdue

1                   Lift1              01/01/19                               Yes

2                   Lift2             08/12/18                                Yes

3                   Lift3             

4                   Lift4             01/01/19                                Yes

5                   Lift5            

6                   Lift6             01/02/19                                No

7                   Lift7

8                   Lift8

9                   Lift9

See below PBIX:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYL_-CFbONVzOLWDA

Is there any way I can change the 'Overdue' and 'Report Date' columns from a blank value to 'No Report' where there is no data?

Also, for Lift 2, the value returned for Overdue column is incorrect - it should be No but in PBI it is Yes. Any idea how to fix this?

The table is returning all records, so is not honouring the slicer on the Customer table

And finally, I need to create a slicer for the Overdue column, so I can select from Yes/No/No Report. But not sure how to do this if I cant even get the correct data into the table 

Cheers for all help

[AlB · Accepted solution]

@ansa_naz

MLatestReportDate = 
VAR _Max =
    MAX ( Reports[Report date] )
VAR _CustomersLifts =
    CALCULATETABLE (
        DISTINCT ( Lifts[Lift ID] );
        ALL ( Lifts )
    )
RETURN
    IF (
        SELECTEDVALUE ( Lifts[Lift ID] )
            IN _CustomersLifts;
        IF (
            ISBLANK ( _Max );
            "No report";
            _Max
        )
    )

MLastOverdue = 
VAR _LastDate =
    MAX ( Reports[Report date] )
VAR _CustomersLifts =
    CALCULATETABLE (
        DISTINCT ( Lifts[Lift ID] );
        ALL ( Lifts )
    )
RETURN
    IF (
        SELECTEDVALUE ( Lifts[Lift ID] )
            IN _CustomersLifts;
        IF (
            ISBLANK ( _LastDate );
            "No report";
            CALCULATE (
                DISTINCT ( Reports[Overdue] );
                Reports[Report date] = _LastDate
            )
        )
    )

[AlB]

Hi @ansa_naz

I think you'll have to create two explicit measures instead of relying on implicit ones.

Here is your file with the new measures.

MLatestReportDate =
VAR _Max =
    MAX ( Reports[Report date] )
RETURN
    IF (
        ISBLANK ( _Max );
        "No report";
        _Max
    )

MLastOverdue =
VAR _LastDate =
    MAX ( Reports[Report date] )
RETURN
    IF (
        ISBLANK ( _LastDate );
        "No report";
        CALCULATE (
            DISTINCT ( Reports[Overdue] );
            Reports[Report date] = _LastDate
        )
    )

[ansa_naz]

Thank you for this @AlB. Any idea how I can get the measure to honour other slicers/filters being applied to this visual? Currently, the measure ignores all the filters/visuals and adds every single Lift record into the whole table, whether they should be filtered out or not

Cheers for your help

[ansa_naz]

I have amended the original PBI example file, to include a customer slicer. This mirrors my own needs as I have a customer slicer too. With the measure as it is, the visual ignores the customer slicer and returns all values:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYL_-CFbONVzOLWDA

So my tables are now:

Lifts table:

LiftID      Customer         LiftRef

1             A                        Lift1

2             A                       Lift2

3             B                        Lift3

4             C                        Lift4

5             D                       Lift5

6             E                        Lift6

7             E                        Lift7

8             F                        Lift8

9             F                        Lift9

Customer table:

Customer

A

B

C

D

E

F

Reports table:

LiftID        Report Date              Overdue

1              01/01/19                   Yes

1              15/06/18                   No

1              12/04/18                   No

2              15/09/18                   Yes

2              08/12/18                  No

4              01/01/19                  Yes

6              01/02/19                  No

I have a slicer on the Customer table, to exclude customer F, and I would like to return the following:

LiftID            LiftRef           Latest Report Date                Overdue

1                   Lift1              01/01/19                               Yes

2                   Lift2             08/12/18                                No

3                   Lift3             No Report                              No Report

4                   Lift4             01/01/19                                Yes

5                   Lift5             No Report                              No Report

6                   Lift6             01/02/19                                No

7                   Lift7            No Report                               No Report

However I am getting:

LiftID            LiftRef           Latest Report Date                Overdue

1                   Lift1              01/01/19                               Yes

2                   Lift2             08/12/18                                Yes

3                   Lift3             

4                   Lift4             01/01/19                                Yes

5                   Lift5            

6                   Lift6             01/02/19                                No

7                   Lift7

8                   Lift8

9                   Lift9

Any ideas please?

Cheers

[AlB · Accepted solution]

@ansa_naz

MLatestReportDate = 
VAR _Max =
    MAX ( Reports[Report date] )
VAR _CustomersLifts =
    CALCULATETABLE (
        DISTINCT ( Lifts[Lift ID] );
        ALL ( Lifts )
    )
RETURN
    IF (
        SELECTEDVALUE ( Lifts[Lift ID] )
            IN _CustomersLifts;
        IF (
            ISBLANK ( _Max );
            "No report";
            _Max
        )
    )

MLastOverdue = 
VAR _LastDate =
    MAX ( Reports[Report date] )
VAR _CustomersLifts =
    CALCULATETABLE (
        DISTINCT ( Lifts[Lift ID] );
        ALL ( Lifts )
    )
RETURN
    IF (
        SELECTEDVALUE ( Lifts[Lift ID] )
            IN _CustomersLifts;
        IF (
            ISBLANK ( _LastDate );
            "No report";
            CALCULATE (
                DISTINCT ( Reports[Overdue] );
                Reports[Report date] = _LastDate
            )
        )
    )

[ansa_naz]

Hi @AlB, I have tried both of these but I am getting syntax errors, could you please help? I have added these measures to the below shared PBIX file, but cant figure out why it is complaining:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYL_-CFbONVzOLWDA

Sorry for using up more of your time!

Naz

[ansa_naz · Accepted solution]

Hi @AlB, think I have figured it out, I needed to swap out the semi colons for commas, and it now works. Thanks for your help!

Changed to:

MLastOverdue = 
VAR _LastDate =
    MAX ( Reports[Report date] )
VAR _CustomersLifts =
    CALCULATETABLE (
        DISTINCT ( Lifts[Lift ID] ),
        ALL ( Lifts )
    )
RETURN
    IF (
        SELECTEDVALUE ( Lifts[Lift ID] )
            IN _CustomersLifts,
        IF (
            ISBLANK ( _LastDate ),
            "No report",
            CALCULATE (
                DISTINCT ( Reports[Overdue] ),
                Reports[Report date] = _LastDate
            )
        )
    )

MLatestReportDate = 
VAR _Max =
    MAX ( Reports[Report date] )
VAR _CustomersLifts =
    CALCULATETABLE (
        DISTINCT ( Lifts[Lift ID] ),
        ALL ( Lifts )
    )
RETURN
    IF (
        SELECTEDVALUE ( Lifts[Lift ID] )
            IN _CustomersLifts,
        IF (
            ISBLANK ( _Max ),
            "No report",
            _Max
        )
    )

Thank you for your help! 

Naz