Records that do not exist in the table

[rmolinajr]

Hi, 

I'm having trouble creating a report and was wondering if anyone can help me.

The report needs to do the following:

Table DataBase:

Name	Salary	Payday
Robert	1000	05-09-2017
Robert	3000	07-07-2017
Robert	2000	08-10-2017
Lilian	3000	07-07-2017
Lilian	500	08-10-2017
Lilian	0	06-10-2017
Kelly	3000	05-02-2017
Kelly	500	06-20-2017
Kelly	500	07-02-2017
Kelly	50	08-02-2017

There is this sample information and when I load in power bi, I want to create a matrix table and the result would look like this:

I did not select anything in the filter and then all the reports appear.

But what I need to do, everyone with zero and zero would need to disappear.

Choosing the filter, bringing the month 6 to Lilian parace again, because it does not have more months with 0.

I made a calculation, example:

.Sum_Salary = SUM(Tabela1[Salary]) 

.Min_Salary = MINX(ALLSELECTED(Tabela1[Month]); [.Sum_Salary])

Placing the .Min_Salary measurement in "Visual level filters" will remove the expected results.

See the image below:

This result that vanished Lilian was because I put the measure .Min_Salary> 0 in the filtering.

But the problem is Robert who has not disappeared, he has the value of the month 6 null, because it has no value in that month, so it should disappear, that I'm in trouble, I can not remove this line.

Choosing the filter, bringing the month 6 to Lilian parace again, because it does not have more months with 0.

Who can help me, thank you.

[v-jiascu-msft]

Hi @rmolinajr,

Could you please mark the proper answer as solution or share the answer if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

Hi @rmolinajr,

The easiest way may be adding the missing values of some months. When I added the blank values to the table, the result was easy to filter.

Maybe we could create a new table with all the month data.

Test =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( 'Tabela1'[Name] ), VALUES ( 'Tabela1'[Month] ) ),
    "paydate", LOOKUPVALUE ( Tabela1[Payday], Tabela1[Month], [Month], Tabela1[Name], [Name] ),
    "newsalary", IF (
        ISBLANK (
            LOOKUPVALUE ( Tabela1[Salary], Tabela1[Name], [Name], Tabela1[Month], [Month] )
        ),
        0,
        LOOKUPVALUE ( Tabela1[Salary], Tabela1[Name], [Name], Tabela1[Month], [Month] )
    )
)

Then it's easy to filter data.

Best Regards!

Dale