Forum Discussion
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
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
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
- It is a measure ,it's not a column
Then you can right the measure with IF function which will return 0 if your logic is not true.
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
I have a similar issue wherein I have to add missing seconds row as zero. What changes should be made to the DAX code.
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
