I have created Matrxi visual in Power BI
My matrxi rows:year,Month,Day(date hierachy)
Source:Azure sql server query
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
Expected OUTPUT table2:
Go to Solution.
1. Create a new table
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]))
Community Support Team _ Maggie LiIf 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
Do you import data in to Power BI Desktop or use direct query/ live connection?
My data is import data method,
Learn how to create your own user groups today!
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!