Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calendar slicer for visuals based on multiple tables.

Hi.

 

I'm trying to build a report where I have 3 different visuals on a single page, with each visual getting filtered by a single date hierarchy slicer. I'm relatively inexperienced when it comes to Power BI and I can't seem to figure out how to accomplish this.

 

My data model consists of 4 Sharepoint Lists: User, Daily, Weekly and Monthly. There's a relation between User table and the other tables: basicly, 1 row in Daily/Weekly/Monthly lists is a single users Daily/Weekly/Monthly tasks. There is no relation between the Daily/Weekly/Monthly tables. The Daily list has a date column, and the Weekly/Monthly lists have a weeknum/monthnum columns. 

 

What I would like to achieve is have a visual from each list on a single page, with two slicers: one to choose daterange from the dates in the Daily list, and a second one with a Year / Qtr / Month / Week hierarchy. I have the first slicer working, but I'm having some trouble with the second one.

 

My initial idea was to make a separate calendar table, which I got working when using only one of the lists, however when I try to make a relation to a second table, I get a circular dependency error.

 

Is there a way to achieve what I'm trying to do, or should I look for a different approach. I'm happy to provide more info / context if my post isn't clear enough.

 

Thanks in advance,

Aapok

Need Help

2 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Based on your description, it seems a bit vague. Can you provide the relevant screenshot information and describe it so that I can answer it for you as soon as possible?

     

    If you want to implement a slicer to filter a specified list, you can try using the following function.

    Power BI - Edit Interactions features - Power BI Docs

     

    For similar circular dependency problems, you can refer to the following solutions.

    Solved: "A circular Dependency was detected" error while c... - Microsoft Power BI Community

    Solved: Circular Dependency Error - Microsoft Power BI Community


    Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, thanks for taking the time to respond to my topic.

       

      I actually managed to find a solution on my own, but I will try to clarify the issue I was having and post my solution, maybe it can help someone else with a similar problem. I won't be providing screenshots of the visuals due to them containing sensitive data (names etc). 

       

      I wanted to have a Date hierarchy slicer (Year/Month/Week) that would filter my visuals based on three different sharepoint lists. The problem I was having was that only one of the lists had a date field (the list containing daily tasks), with the other two having a calculated Week and Month columns (generated through PowerApps) as numbers. 

       

      What I ended up doing was using the Created column on the weekly/monthly lists to calculate the first day of the week/month respectively, and creating a relationship between those columns and my calendar table. 

       

      Relations between my Calendar table and SP Lists

       

      I'm sure there is a better solution, but due to my minimal experience with Power BI this is the one I managed to come up with 🙂