Filling missing value for non existing row in report List

[joao_maia_ext]

Hello Power Bi Community!

In this process of get familiarized with the powerbi I'm still facing some setbacks and i would like to ask for some support!

For this case study i've 3 tables:

1. Main List of IND ( ID of Indicators)
2. Main List of dates (ID of Quarters)
3. List: Reported Results

In table 1 I have all the IND IDs' and in the table 2 I have all the quarters (T.1, T.2, T,3 and T.4).

In my list 3 (Reported) I have only the value IND reported for some given quarters.

I would like to compile the results in one matrix and chart but I'm facing some issues.

This is would it looks like:

My tables:

My objective:

For the missing values at the compiled matrix I would like to add - "Not reported" and then I want to compile all that in one chart, that counts the numbers of occurrences.

At the moment I've tried this measure but i can't make the chart.. it does not allow to add the measure to it..

=SUMMARIZE('Table1',"Report",if(ISBLANK([Result]),"Not Reported",[Result]))

Can someone help me find a solution?

Best regards!

JM

[joao_maia_ext]

It worked!! thanks 🙂

[DataInsights · Accepted solution]

@joao_maia_ext,

Try this solution.

1. Create data model:

2. Create measure:

Result Adj = IF ( ISBLANK ( MAX ( Table3[Result] ) ), "Not Reported", MAX ( Table3[Result] ) )

3. Create calculated table:

Visual Table = 
VAR vTable =
    CROSSJOIN ( VALUES ( Table1[ID IND] ), VALUES ( Table2[Quarter] ) )
VAR vResult =
    ADDCOLUMNS ( vTable, "Result", [Result Adj] )
RETURN
    vResult

4. Create measure:

Count Result = COUNT ( 'Visual Table'[Result] )

5. Create visuals using the calculated table:

Note: The matrix can be produced without the calculated table, as follows:

1. The row and column fields are from the dimension tables (Table1 and Table2).

2. The row and column fields are set to "Show items with no data".

3. The values field is [Result Adj].