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%  is equal to 100%)

for missing rows we need to consider   0/1 = 0 %  ,   we need to aggregate fro year with 0 percentage  for missing rows. how to achieve that 

 

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

     

7 Replies

  • dilumd's avatar
    dilumd
    Icon for Impactful Individual rankImpactful Individual
    9 years ago

    Hi

     

    You can do this in query editor (right click on the relevant column) and replace "null" with "0" as shown below.

     

    Hope this helps

     

     

      • dilumd's avatar
        dilumd
        Icon for Impactful Individual rankImpactful Individual
        9 years ago

        Then you can right the measure with IF function which will return 0 if your logic is not true.

  • 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.

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

    Hi Surendra_thota,

     

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

     

    Best Regards!
    Dale