cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Matrxi missing day data zero

Hi team,

I have created Matrxi visual in Power BI

My matrxi rows:year,Month,Day(date hierachy)

Columns:INDIA,USA,UK

Source:Azure sql server query

MY question:

When day data not availble my source query should be zero in columns

table1:current my data

table 2:Expected output(4 and 7 day data not availble in source(table1) but i am expecting 4 and 7 date should be zero values

 

Table1:

YearMONTHDayINDIAUSAUK
201911202010
201912401030
201913201040
201915404044
201916202022
20191840622
201919239044

 

 

 

Expected OUTPUT table2:

YearMONTHDayINDIAUSAUK
201911202010
201912401030
201913201040
201914000
201915404044
201916202022
201917000
20191840622
201919239044

Thanks

Shanvitha

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

1. Create a new table

calendar =
ADDCOLUMNS (
    CALENDAR (
        DATE ( CALCULATE ( MIN ( 'your table'[Year] ), ALL ( 'your table' ) ), 1, 1 ),
        DATE ( CALCULATE ( MAX ( 'your table'[Year] ), ALL ( 'your table' ) ), 12, 31 )
    ),
    "calendar_year", YEAR ( [Date] ),
    "calendar_month", MONTH ( [Date] ),
    "calendar_day", DAY ( [Date] )
)

2. Create calculated columns in your table and new table

in calendar table
merge1 = [calendar_year]&"-"&[calendar_month]&"-"&[calendar_day]

in your table
merge2 = [Year]&"-"&[MONTH]&"-"&[Day]

3. create relationships as below

6.png

4. create measures in your table

Measure = IF(SUM('your table'[INDIA])=BLANK(),0,SUM('your table'[INDIA]))

Measure 2 = IF(SUM('your table'[UK])=BLANK(),0,SUM('your table'[UK]))

Measure 3 = IF(SUM('your table'[USA])=BLANK(),0,SUM('your table'[USA]))

4.png

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

1. Create a new table

calendar =
ADDCOLUMNS (
    CALENDAR (
        DATE ( CALCULATE ( MIN ( 'your table'[Year] ), ALL ( 'your table' ) ), 1, 1 ),
        DATE ( CALCULATE ( MAX ( 'your table'[Year] ), ALL ( 'your table' ) ), 12, 31 )
    ),
    "calendar_year", YEAR ( [Date] ),
    "calendar_month", MONTH ( [Date] ),
    "calendar_day", DAY ( [Date] )
)

2. Create calculated columns in your table and new table

in calendar table
merge1 = [calendar_year]&"-"&[calendar_month]&"-"&[calendar_day]

in your table
merge2 = [Year]&"-"&[MONTH]&"-"&[Day]

3. create relationships as below

6.png

4. create measures in your table

Measure = IF(SUM('your table'[INDIA])=BLANK(),0,SUM('your table'[INDIA]))

Measure 2 = IF(SUM('your table'[UK])=BLANK(),0,SUM('your table'[UK]))

Measure 3 = IF(SUM('your table'[USA])=BLANK(),0,SUM('your table'[USA]))

4.png

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Do you import data in to Power BI Desktop or use direct query/ live connection?

 

Best Regards
Maggie

Anonymous
Not applicable

Hi Maggie,

 

My data is import data method,

 

thanks

Shanvitha

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!