topic Re: How to test for blank values in matrix visual and replace them with a previous value from the table? in DAX Commands and Tips

How to test for blank values in matrix visual and replace them with a previous value from the table?

szabofe — Tue, 29 Nov 2022 05:08:04 GMT

Hi Community,

I'd like to make a report based on occationally entered soc. media data.
The user enters data related to a specific date and team by soc. media.
How many followers the team has on the specific date?
There are missing weeks, months like this:
(The MAX of the timeperiod can be seen.
I'd like to put a visual on the report witch shows the trend in the end.)

There are two tables in the sample:

I wrote a measure to fill in the matrix cells.
The returning value would have to corrigate the missing values.
In place of 101010 will come a measure.
(... why don't we have DAX formatting here... )

_MaxFollowerCount = VAR dateMinDateInPeriod = MINX( VALUES('Table'[id]); VAR dateMin = CALCULATE( MIN('DateTable'[Date]); RELATEDTABLE(DateTable) ) RETURN dateMin ) VAR intMAXinPeriod = MAXX( 'Table'; VAR intMax = MAX('Table'[Value]) RETURN IF( ISBLANK(intMax) || intMax = 0; 101010; intMax ) ) RETURN intMAXinPeriod

My problem is that:
The value in the blank cells is not BLANK() nor 0 ...
I never get back 101010...
What is my failure or missunderstanding?

Thank you for any help.

Regards,
F.

Re: How to test for blank values in matrix visual and replace them with a previous value from the table?

Anonymous — Tue, 29 Nov 2022 09:07:23 GMT

Hi szabofe ,

Please share some sample data so that we could test the formula.

Best Regards,

Jay

Re: How to test for blank values in matrix visual and replace them with a previous value from the table?

szabofe — Tue, 29 Nov 2022 10:43:26 GMT

Hi Jay,

You can download my sample from the link below.
TestSocMed.pbix
Thank you for your help.
I got a bit forward in the solution.
Probably the direction of the iteration in the MAXX sould be on the DateTable side...

VAR tableDateTableFiltered = FILTER( VALUES(DateTable[Date]), DateTable[Date] >= dateMinDateInTable && DateTable[Date] <= dateMaxDateInTable ) VAR intMAXinPeriod = MAXX( tableDateTableFiltered, VAR intMax = MAX('Table'[Value]) RETURN COALESCE( intMax, 101010 ) )

Now the matrix looks like this:

Now I'm fighting with the right measure that gives me back the previous value in the series
- the value from the before column, that precedes 101010.

Re: How to test for blank values in matrix visual and replace them with a previous value from the table?

szabofe — Wed, 07 Dec 2022 08:29:48 GMT

Hi Jay,

What is your opinion?
Am I on the right track?

Thanks and regards,
F.