Forum Discussion

bzeeblitz's avatar
bzeeblitz
Helper IV
1 year ago

Thanks a lot.

 

But this point I'm not sure how to hook output to the date dimension table in

power bi 

 

If not, what you will need to do in Power BI is hook the output to a date dimension table and then you will have every available date and you can put a count column in your report that will show a "0" for any of the dates that do not exist in SharePoint.

v-nmadadi-msft's avatar
v-nmadadi-msft
Community Support
1 year ago

Hi  bzeeblitz,
Thanks for reaching out to the Microsoft fabric community forum.


Thanks collinq for you valuable points.

You can create a data table in power BI using this DAX command

DateTable = ADDCOLUMNS (

    CALENDAR (DATE(2024,1,1), TODAY()),  

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMMM")

)



Modify the start date as you seem fit.

Then  Create a Relationship, navigate to Model View in Power BI and establish a one-to-many relationship between the DateTable[Date] column from the Date Table and the [Date] column in your fact table, which contains the count values.

Finally Create a new measure that ensures missing dates show 0:

TotalCount =

VAR CountValue = SUM('FactTable'[Count])

RETURN

IF( ISBLANK(CountValue), 0, CountValue)


Now, whenever there is no data for a date, the count will show 0 instead of being completely omitted.

Hope this workaround will fix your issue in sharepoint.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards