Hiding the empty rows in a running total

[ansa_naz]

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

[Anonymous]

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]) )
)

[AlB]

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
    )

[ansa_naz]

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!

[AlB]

@ansa_naz 

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] )
    )
)

[ansa_naz]

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