Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

    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]))

     

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

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

     

    Best Regards
    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie,

       

      My data is import data method,

       

      thanks

      Shanvitha

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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

    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]))

     

    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.