Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 ) ) )
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
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 ) )
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
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
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 ) ) )
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |