Forum Discussion

szabofe's avatar
szabofe
Frequent Visitor
3 years ago

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

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.

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi szabofe ,

     

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

     

    Best Regards,

    Jay

    • szabofe's avatar
      szabofe
      Frequent Visitor
      3 years ago

      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.

    • szabofe's avatar
      szabofe
      Frequent Visitor
      3 years ago

      Hi Jay, 

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

      Thanks and regards, 
      F.