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
I have a running total being calculated, but it is returning blank values where there is no data for the filters applied.
Please see example data below:
Hours table:
Date | Employee | Hrs | Job |
20/12/2018 | A | 18 | 2000 |
25/12/2018 | B | 5 | 1000 |
29/12/2018 | B | 9 | 1000 |
01/01/2019 | A | 5 | 1000 |
02/01/2019 | B | 2 | 1000 |
03/01/2019 | B | 4 | 2000 |
04/01/2019 | B | 5 | 1000 |
05/01/2019 | A | 1 | 1000 |
06/01/2019 | A | 4 | 1000 |
07/01/2019 | A | 5 | 1000 |
08/01/2019 | A | 7 | 1000 |
09/01/2019 | A | 1 | 1000 |
10/01/2019 | A | 1 | 1000 |
11/01/2019 | A | 8 | 1000 |
12/01/2019 | B | 4 | 1000 |
13/01/2019 | A | 8 | 1000 |
14/01/2019 | A | 12 | 1000 |
15/01/2019 | A | 16 | 1000 |
FHours table:
Date | Hrs | Job |
01/11/2018 | 50 | 1000 |
02/12/2018 | 150 | 2000 |
06/01/2019 | 5 | 1000 |
08/03/2019 | 18 | 1000 |
Matrix table:
Job | Lead |
1000 | A |
2000 | B |
The relationships are:
Hours (Job) MANY*---1ONE Matrix (Job)
Matrix (Job) ONE1---MANY* FHours (Job)
I have two measures:
HrsActualR = CALCULATE( SUM('Hours'[Hrs]), FILTER( ALLSELECTED('Hours') , 'Hours'[Date]<=max(Hours[Date]) && Hours[Employee]=distinct('Matrix'[Lead]) ) )
HrsRunningF = CALCULATE( SUM('FHours'[Hrs]) )
I have a slicer to select the Job from Matrix table. I want to show the below in a table visual:
Date | HrsActualR | HrsRunningF |
01/01/2019 | 5 | 73 |
05/01/2019 | 6 | 73 |
06/01/2019 | 10 | 73 |
07/01/2019 | 15 | 73 |
08/01/2019 | 22 | 73 |
09/01/2019 | 23 | 73 |
10/01/2019 | 24 | 73 |
11/01/2019 | 32 | 73 |
13/01/2019 | 40 | 73 |
14/01/2019 | 52 | 73 |
15/01/2019 | 68 | 73 |
However what I am getting is:
Is there any way to get the data I need, without the blank/duplicate entries?
See below the PBIX if this would help with this:
https://1drv.ms/u/s!AuiIgc_S9J5JhbYTFO35ar1ki8imjw
Many thanks for all help
if you add the top line to your HrsRunningF measure it will only show when there is a value for HrsActualR
HrsRunningF = IF ( NOT( ISBLANK( [HrsActualR])), CALCULATE( SUM('FHours'[Hrs]) ) )
Hi @ansa_naz
Note this is using your measures as defined in your pbix
If you just want to avoid showing the blanks in [HrsActualR] you can create a measure like this, place it in visual level filters and select 'Show items when the value is' --> 1
ShowMeasure = IF ( NOT ISBLANK ( [HrsActualR] ); 1 )
If you want to show only the first appearance of each pair ([HrsActualR] , [HrsRunningF]) as you seem to imply in your expected result, you can create the following (rather complex) measure instead. Place it in visual level filters and select 'Show items when the value is' --> 1. Most likely there are simpler ways to accomplish this by modifying your measures but well, it seems to do the job.
By the way, I've noticed that the set-up you have in the pbix throws an error when both 1000 and 2000 are selected in the slicer.
ShowMeasure2 = VAR _AuxTable = FILTER ( ADDCOLUMNS ( CALCULATETABLE ( DISTINCT ( Hours[Date] ); ALL ( Hours[Date] ) ); "Meas1"; [HrsActualR]; "Meas2"; [HrsRunningF] ); NOT ISBLANK ( [Meas1] ) ) VAR _ResTable = FILTER ( _AuxTable; Hours[Date] = MINX ( _AuxTable; IF ( [Meas1] = EARLIER ( [Meas1] ) && [Meas2] = EARLIER ( [Meas2] ); Hours[Date] ) ) ) RETURN IF ( CONTAINSROW ( _ResTable; SELECTEDVALUE ( Hours[Date] ); [HrsActualR]; [HrsRunningF] ); 1 )
Hi @AlB thanks for your response. @Anonymous has suggested a solution for the blanks which works very well.
As for the duplicates, they are showing because on some days there are hours posted for the correct job but for a different Employee ID - my DAX is only looking for hours posted for the Matrix[Lead] employee ID for that job. However, the visual still shows the date - I guess the most elegant way would be to exclude dates where the hours posted are not for the required Employee ID - but not sure how to do that. I have tried your solution below, and it does work, just a bit worried it may exclude data which is needed? As I am not too sure what it is doing. But many thanks for this, I will use it and keep checking it to see if any issues arise
Any ideas on how I could fix the issue with multiple jobs being selected in the slicer? Didnt even see that, thanks for the spot @AlB
Many thanks!
I haven't understood completely what your measures are all about but try this plus the measure Nick suggested:
HrsActualR_V2 = IF ( NOT ISBLANK ( CALCULATE ( SUM ( 'Hours'[Hrs] ); Hours[Employee] IN DISTINCT ( 'Matrix'[Lead] ) ) ); CALCULATE ( SUM ( 'Hours'[Hrs] ); FILTER ( ALL ( 'Hours'[Date] ); 'Hours'[Date] <= MAX ( Hours[Date] ) ); Hours[Employee] IN DISTINCT ( 'Matrix'[Lead] ) ) )
Hi @Anonymous thanks for your reply, I have amended the measure per your suggestion and it is nearly there, however, the duplicates on the HrsActualR measure are still present - any ideas how to resolve that?
This is what I have now:
But this is what I need:
Date | HrsActualR | HrsRunningF |
01/01/2019 | 5 | 73 |
05/01/2019 | 6 | 73 |
06/01/2019 | 10 | 73 |
07/01/2019 | 15 | 73 |
08/01/2019 | 22 | 73 |
09/01/2019 | 23 | 73 |
10/01/2019 | 24 | 73 |
11/01/2019 | 32 | 73 |
13/01/2019 | 40 | 73 |
14/01/2019 | 52 | 73 |
15/01/2019 | 68 | 73 |
Cheers
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 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |