Forum Discussion

Ghaida789's avatar
Ghaida789
New Member
1 year ago
Solved

Issue with Table Relationships and Filtering Quarterly Data in Power BI

Problem:

I have multiple main tables containing operational data, each with years and quarterly data. I need to create relationships between these tables and a separate table that contains only years and quarters so that I can filter all tables based on the selected year and quarter. Then, make the bookmarks and the buttons. 

 

-Data Structure:

1- Main Tables:

Each main table contains:

  • Year 
  • Quarter 
  • Various operational metrics (e.g., number of meetings, sales, performance, etc.)
2- Years & Quarters Table 

the table includes:

  • Year 
  • Quarter (Q1, Q2, Q3, Q4)

 

The Issue I’m Facing:

1- When creating the relationship between the main tables and yearandQ table, not all years appear in the relationship, even though they are present in Data View.
2- When using Slicers to filter by a specific year and quarter, some visuals show "Blank" and not all data filters correctly.
3- All relationships are set to One-to-Many (1:*), but some tables do not respond to filters, or some quarters are missing.

Solutions I Have Tried:

- Ensured that the data type (Data type) for the Year and Quarter columns matches (whole number for Year and text for Quarter).
- Enabled the relationship using "Make this relationship active."
-Changed the "Cross-Filter Direction" to "Both" instead of "Single."

Example Data Structure:

Main Table Example

YearQMeetingsnumber of Employees
2022Q115020
2022Q218022
2022Q314021
2022Q416023
2023Q117024
2023Q219025

Years & Quarters Table 

YearQuarter

2022Q1
2022Q2
2022Q3
2022Q4
2023Q1
2023Q2
2023Q3
2023Q4

Pictures:

The visual shows blank when filtering the data to 2022How the data appears in the table viewModel viewWhen creating a relationship, the data is missing not all years are shown

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

What I Need Help With:

- How can I correctly set up the relationship so that filtering by year and quarter applies across all tables?
- Is there a better way to link all the tables to the Years & Quarters table to ensure filtering works properly?
- What are the best practices for managing multiple main tables when working with relationships in Power BI?

 

Any advice or suggestions would be greatly appreciated. Thank you!

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

    I would go for using a unique table to gather all the data related to dates.

    Your Years & Quarters table should have:

    • Year as a Whole Number
    • Quarter as a Text (Q1, Q2, Q3, Q4).

    Your main tables should have the same structure for Year and Quarter.

    I think he relationship is not working properly because Year and Quarter are separate columns.

    Create a concatenated column in the Years & Quarters table :

    YearQuarter = CONCATENATE(YearsAndQuarters[Year], YearsAndQuarters[Quarter])

    Do the same for each main table:

    YearQuarter = CONCATENATE(MainTable[Year], MainTable[Q])

    Create one-to-many (1:*) relationship between Years & Quarters[YearQuarter] and each main table's YearQuarter and set the Cross-Filter Direction to "Single", unless necessary to use "Both."

  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    I would create a Date column in the base table (from the Year and Quarter column) and then build a Calendar table.  In the Calendar table, create calculated column formulas for Year, Month name, Month number and Quarter.  Create a relationship (Many to One and Single) from the Date column of the Base data table to the Date column of the Calendar table.

6 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    1 year ago

    I would go for using a unique table to gather all the data related to dates.

    Your Years & Quarters table should have:

    • Year as a Whole Number
    • Quarter as a Text (Q1, Q2, Q3, Q4).

    Your main tables should have the same structure for Year and Quarter.

    I think he relationship is not working properly because Year and Quarter are separate columns.

    Create a concatenated column in the Years & Quarters table :

    YearQuarter = CONCATENATE(YearsAndQuarters[Year], YearsAndQuarters[Quarter])

    Do the same for each main table:

    YearQuarter = CONCATENATE(MainTable[Year], MainTable[Q])

    Create one-to-many (1:*) relationship between Years & Quarters[YearQuarter] and each main table's YearQuarter and set the Cross-Filter Direction to "Single", unless necessary to use "Both."

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    I would create a Date column in the base table (from the Year and Quarter column) and then build a Calendar table.  In the Calendar table, create calculated column formulas for Year, Month name, Month number and Quarter.  Create a relationship (Many to One and Single) from the Date column of the Base data table to the Date column of the Calendar table.

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    1 year ago

    THI Ghaida789,

     

    Thankyou Ashish_Mathur, AmiraBedh  for your reply on the issue.

     

    I'm glad to hear that your query was resolved! If the response provided by the community member addressed your concern, kindly confirm.

    Marking it as Accept Answer and give us Kudos if you found it helpful allows us to ensure that the solutions shared are valuable for the entire community.

     

    If you have any further questions, feel free to reach out!

    • v-sgandrathi's avatar
      v-sgandrathi
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Ghaida789 ,

      I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

       

      Thank you.

      • v-sgandrathi's avatar
        v-sgandrathi
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi Ghaida789 

        May I ask if you have gotten this issue resolved?

        If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

         

        Thank you.

  • HarishKM's avatar
    HarishKM
    Icon for Super User rankSuper User
    1 year ago

    Ghaida789 Hey,
    I will recommend to create a calender table as per below dax.

    Calender = ADDCOLUMNS(CALENDAR(DATE(2000,1,1),DATE(2060,12,31)),
    "year",YEAR([Date]),
    "Month", MONTH([Date]),
    "Month-year",FORMAT([Date],"MMM-YY"),
    "Qtr",QUARTER([Date]),
    "QTR-YY","Q"&"-"&FORMAT([Date],"YY"),
    "IsWorkingDay",
            IF (
                WEEKDAY([Date], 2) <= 5,  // 2 = Monday as the first day of the week (1 = Sunday, 7 = Saturday)
                "Yes",
                "No"
            )
        )


     
     

     

    Thanks
    Harish M
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query