Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Replace empty values in a matrix with the previous non blank value

Hello , I have this matrix that shows the total number of differenet counts , I need to replace empty values with the previous total as shown in the screens :        Thank you in advance...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Thanks @tamerj1  for the answer. It unfortnathly didn't show the expected result .

    I finally created Date.WeekOfYear column and the totals were grouped accordinaly , so no emty values were left

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous 

This dhould work. You csn replace the outer MAXX with SUMX to sum the rows at total level 

Measure1 =
VAR SelectedTable =
ALLSELECTED ( 'kpi TotalErrorHistory' )
VAR CurrentDate =
CALCULATE (
MAX ( 'kpi TotalErrorHistory'[Date] ),
ALL ( 'kpi TotalErrorHistory'[KPIDescription] )
)
RETURN
MAXX (
VALUES ( 'kpi TotalErrorHistory'[KPIDescription] ),
MAXX (
TOPN (
1,
FILTER (
SelectedTable,
VAR KPIDesc = 'kpi TotalErrorHistory'[KPIDescription]
RETURN
'kpi TotalErrorHistory'[Date] <= CurrentDate
&& 'kpi TotalErrorHistory'[KPIDescription] = KPIDesc
),
'kpi TotalErrorHistory'[Date]
),
'kpi TotalErrorHistory'[TotalNumber]
)
)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

tamerj1 

Unfortunathly still the same result as in the previous screen

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous 

    Would you please place tge following measure in the matrix values and let me know what result do you get?

    CALCULATE (
    MAX ( 'kpi TotalErrorHistory'[Date] ),
    ALL ( 'kpi TotalErrorHistory'[KPIDescription] )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      tamerj1 This measure shows the date next to the values that are not empty

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous 

        Apologies for the late response. I got engaged with something else. 

        This has to be a dimension table. Create a new calculated table

        Dates = ALLNOBLANKROW ( 'kpi TotalErrorHistory'[Date] )

         

        create the relationship between the two tables. Place the new dimension date column in the columns of the matrix instead of tge original one. Tyen try the following measure in the values. Remember you can change the MAXX to SUMX based on your requirement. 

         

        Measure1 =
        VAR SelectedTable =
        ALLSELECTED ( 'kpi TotalErrorHistory' )
        VAR CurrentDate =
        MAX ( 'Dates'[Date] )
        RETURN
        MAXX (
        VALUES ( 'kpi TotalErrorHistory'[KPIDescription] ),
        SUMX (
        TOPN (
        1,
        FILTER (
        SelectedTable,
        VAR KPIDesc = 'kpi TotalErrorHistory'[KPIDescription]
        RETURN
        'kpi TotalErrorHistory'[Date] <= CurrentDate
        && 'kpi TotalErrorHistory'[KPIDescription] = KPIDesc
        ),
        'kpi TotalErrorHistory'[Date]
        ),
        'kpi TotalErrorHistory'[TotalNumber]
        )
        )