Show selected dates and only data for the selected dates

[Frankee]

Hello EDNA - this is my first post. Looking for some assistance. I’m 2/3rd’s of the way to the result I need. the attached Excel file, far left tab shows the measure I’m currently working with.

This is a three part measure.

1. Sum total. DONE
2. Show all selected dates. DONE.
3. Don’t show “EMP ID’s” , if they don’t have data during the selected dates in the slicer.

–YELLOW highlighted rows should not be displayed because they don’t have any data during the current selected dates via the slicer.

Any and all help would be much appreciated.
WE080921-081521.xlsx (1.2 MB)

Thank you in advance,

Frankee

[Melissa]

Hi @Frankee,

Try to switch the condition in your IF statement:

TotalCost-IncludeZero :=
IF(
    NOT ( ISBLANK( SUMX( PBFTrack, PBFTrack[LINE ITEM TOTAL] ) ) ),
    SUMX( PBFTrack, PBFTrack[LINE ITEM TOTAL] )
)

The third parameter is optional and will return a blank when omitted.
Pro tip. Store the SUMX calculation result in a variable and refer to that multiple times.

I hope this is helpful.

[Harsh]

Hello @Frankee,

Thank You for posting your query onto the Forum.

Well the reason why it’s still showing the results against the EMP ID’s where you actually don’t have records is because of the condition you’ve put into your measure i.e. “”. Below is the measure provided for the reference that you’ve written in your file.

TotalCost-IncludeZero = 
=IF(
	ISBLANK(
		SUMX(
			PBFTrack,
			PBFTrack[LINE ITEM TOTAL]
			)
		),"",
			SUMX(
				PBFTrack,
				PBFTrack[LINE ITEM TOTAL]
				)
)

And due to this entire line of blank results also appear in your table. Below is the screesnot provided for the results provided for the reference -

Now, replace “” with the “BLANK()” and you’ll observe that EMP ID’s which don’t have any records will not show anymore. Below is the revised measure provided for the reference -

TotalCost-IncludeZero =
=IF(
	ISBLANK(
		SUMX(
			PBFTrack,
			PBFTrack[LINE ITEM TOTAL]
			)
		),BLANK(),
			SUMX(
				PBFTrack,
				PBFTrack[LINE ITEM TOTAL]
				)
)

See the screenshot of the final results provided for the reference -

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

[Frankee]

Hey @Melissa , appreciate you looking at this for me.

I just tested the suggested measure and the date of 8/15 is no longer visible, which is what I need.

With your measure, I’m showing 108 "EMP Code"s which is expected. But not showing the date of 8/15, which is what is required even if I have no data on that date.

[Frankee]

Hello @Harsh - I also tested your measure and totals are as expected. but not when it comes to the date columns. Found that it is no longer showing the 8/15 date. I understand it’s not showing 8/15 due to no data on that date.

108 EMP CODE’s are expected with the dates selected via the slicer of 8/9 - 8/15, because they all have data during that period except for 8/15. 8/15 has no data, but because I selected that date in the slicer, I need it to show, 8/15.

I appreciate you taking a look at it. additional ideas would be appreciated.

Thank you for your time,

Frankee

[Melissa]

Hi @Frankee,

First a question are you not allowed to use VAR at all? I don’t like that one bit
Give this a go instead (we’ll just add 4 more iterators to the mix )

TotalCost-IncludeZero :=
IF(
    SUMX(
        ADDCOLUMNS(
            FILTER( ALL( 'Calendar'[Date] ); [DATE] IN ALLSELECTED( 'Calendar'[Date] ) 	 
        );
            "Value";
                IF(
                    NOT ( ISBLANK( SUMX( PBFTrack; PBFTrack[LINE ITEM TOTAL] ) ) );
                    SUMX( PBFTrack; PBFTrack[LINE ITEM TOTAL] )
                )
        ); [Value] ) = BLANK();
    "" ;
    SUMX(
        ADDCOLUMNS(
            FILTER( ALL( 'Calendar'[Date] ); [DATE] IN ALLSELECTED( 'Calendar'[Date] ) 
        );
            "Value";
                IF(
                    NOT ( ISBLANK( SUMX( PBFTrack; PBFTrack[LINE ITEM TOTAL] ) ) );
                    SUMX( PBFTrack; PBFTrack[LINE ITEM TOTAL] )
                )
        );
        [Value]
    )
)

I hope this is helpful.

[Melissa]

@Frankee
Oh I see this in’t working, let me try that again - got another idea

[Frankee]

@Melissa - I forgot to mention in my 1st reply to you that your VAR suggestion was a good suggestion. it should certainly cut down on the code, especially if we see 4 iterators . Let me test now.

Oh and YES, we can use VAR

[Melissa]

Hi @Frankee,

Thanks for that, really helps - give this a go.

TotalCost-IncludeZero :=
VAR _Calc = SUMX( PBFTrack; PBFTrack[LINE ITEM TOTAL] )
VAR mySum = 
	SUMX(
    	ADDCOLUMNS(
        	FILTER( 
				ALL( 'Calendar'[Date] ); 
				[DATE] IN ALLSELECTED( 'Calendar'[Date] ) 
        	); 
			"Value"; IF( NOT ( ISBLANK( _Calc ) ); _Calc )
        ); [Value] 
	)
VAR myTotSum = CALCULATE( SUMX( PBFTrack; PBFTrack[LINE ITEM TOTAL] ); ALLSELECTED('Calendar'[Date]))
RETURN

IF(
    NOT ( ISBLANK( myTotSum ) );
    IF( 
		ISBLANK( mySum );
		"";
		 _Calc 
	)
)

[Frankee]

@Melissa - quick question, what’s the reason for using a semicolon " ; ", as opposed to a comma ", "?

[Melissa]

None, just my list separator…
Amend to your needs.

[Frankee]

Hey @Melissa , Way to go. It worked great. gives me exactly what I was looking for. thank you so very much!