Forum Discussion

Surendra_thota's avatar
Surendra_thota
Icon for Helper II rankHelper II
9 years ago
Solved

How to add Missing rows and add zero % for missing rows

Hi All   we are gettig data from the table for every month , if  some month is missing we need add row . for example  Reading towards complaince / Monthly Frequency Target = complaince%   ( >100% ...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi Surendra_thota,

     

    These missing months don't exist, so let's create them. (Modeling -> New Table -> input this formula)

    Date =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ),
        "Month1", EOMONTH ( [Date], 0 )
    )
)

    Create a relationship from "Month1" to "month";

    Then join the two tables together.

    Table 2 =
NATURALLEFTOUTERJOIN ( 'Date', 'Table1' )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

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

Hi Surendra_thota,

 

These missing months don't exist, so let's create them. (Modeling -> New Table -> input this formula)

Date =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ),
        "Month1", EOMONTH ( [Date], 0 )
    )
)

Create a relationship from "Month1" to "month";

Then join the two tables together.

Table 2 =
NATURALLEFTOUTERJOIN ( 'Date', 'Table1' )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Nakul93's avatar
Nakul93
Regular Visitor
8 years ago

I have a similar issue wherein I have to add missing seconds row as zero. What changes should be made to the DAX code.