topic How to efface blank category row without affecting TOTAL in DAX Commands and Tips

How to efface blank category row without affecting TOTAL

Anonymous — Mon, 08 Aug 2022 08:14:09 GMT

I have created a calculated column with DATESINPERIOD to always show the last 7 days of my report and connected it to my 'Dates' table.
When I apply the measure to the new table, I get a top row with a blank category but with a large number.
So I use HASONEVALUE to eliminate the top row but I end up eliminating the Total as well.

Is there another way to do it without affecting the Total?

Re: How to efface blank category row without affecting TOTAL

amitchandak — Mon, 08 Aug 2022 09:42:55 GMT

Anonymous , Try isinscope, it can check if the date is in visual scope or not. for Grand Total Date will not be scope

if(not(isinscope(Date[Date])), [Measure], <Your code with has one value>)

Also, a blank value in date means, your date table is missing dates. or your date in fact has a time stamp.

You can also try visual level filter

Re: How to efface blank category row without affecting TOTAL

Anonymous — Mon, 08 Aug 2022 12:17:35 GMT

"Also, a blank value in date means, your date table is missing dates. or your date in fact has a time stamp."

So creating a calculated date table with the last 7 days seems to be bad practice.

So what's the best way to always show the last 7 days as I update the report?