cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ansa_naz Member
Member

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

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:

 

Items with no data.jpg

 

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

@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 Member
Member

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

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

 

 

6 REPLIES 6
Super User
Super User

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

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 Member
Member

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

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 Member
Member

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

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

Super User
Super User

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

@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 Member
Member

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

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

 

MLastOverdue.jpg

 

MLatestReportDate.jpg

 

Sorry for using up more of your time!

Naz

ansa_naz Member
Member

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

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