Records that do not exist in the table

Question

Hi,&nbsp;&nbsp;I'm having trouble creating a report and was wondering if anyone can help me.&nbsp;The report needs to do the following:&nbsp;Table DataBase:&nbsp;NameSalaryPaydayRobert100005-09-2017Robert300007-07-2017Robert200008-10-2017Lilian300007-07-2017Lilian50008-10-2017Lilian006-10-2017Kelly300005-02-2017Kelly50006-20-2017Kelly50007-02-2017Kelly5008-02-2017&nbsp;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:&nbsp;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.&nbsp;Choosing the filter, bringing the month 6 to Lilian parace again, because it does not have more months with 0.&nbsp;&nbsp;I made a calculation, example:&nbsp;.Sum_Salary = SUM(Tabela1[Salary])&nbsp;.Min_Salary = MINX(ALLSELECTED(Tabela1[Month]); [.Sum_Salary])&nbsp;Placing the .Min_Salary measurement in "Visual level filters" will remove the expected results.&nbsp;See the image below:&nbsp;This result that vanished Lilian was because I put the measure .Min_Salary&gt; 0 in the filtering.&nbsp;&nbsp;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.&nbsp;Choosing the filter, bringing the month 6 to Lilian parace again, because it does not have more months with 0.&nbsp;&nbsp;Who can help me, thank you.

v-jiascu-msft · Accepted Answer

Hi&nbsp;rmolinajr,&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Best Regards!Dale

Forum Discussion

Records that do not exist in the table

2 Replies

Related content

Replace blank values in table visual for records with existing category

How to Count from two different table Un-matched record with New table

Display parent record when no child record exists

Table Merge creates duplicate records

Edit existing table values

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