Forum Discussion

rmolinajr's avatar
rmolinajr
Frequent Visitor
9 years ago
Solved

Records that do not exist in the table

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...
Need Help
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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