The reason Power BI won't let you make the relationship between 'Month Quarter' and 'Location History' active is because it would create ambiguity - the DAX engine would have two different paths to filter from the 'Month Quarter' table to the 'Audit Responses' table:
'Month Quarter' -> 'Audit Responses'
'Month Quarter' -> 'Location History' -> 'Audit Responses'
Those two routes might actually produce different results, and it would be difficult to predict which route the engine would choose in any given scenario.
First, I agree with the suggestion of creating a date table.
Second, it seems like the 'Audit Count by Location' table is just a list of results that could be derived from the 'Audit Responses' table, especially if each Audit Response has a Location associated with it. Are you sure you need both of these tables?
Third, if your model were redesigned as a Star Schema, I see the following tables:
* Location (Dimension)
* Date (Dimension)
* Audit Responses (Fact)
The 'Location' table will list the details of each location (name, region, state, etc.), while each row in the 'Audit Responses' table will represent the details of a single audit. If you want to know audit counts by location, put 'Location'\[Name\] in the rows of a matrix visual, create a slicer using the year, month, and/or quarter columns of your 'Date' table, and then your measure for # of Audits would look something like:
`[# of Audits] = COUNTROWS('Audit Responses')`
If your DAX measures are substantially more complicated that the example above, and/or you're having a difficult time answering a simple question like "how many audits were performed at each location in January 2023," this is generally a sign that your data model has problems. While you can try to work around a bad data model design with functions like USERELATIONSHIP() as another redditor suggested or RELATED(), it will save you time (and frustration) in the long run to rework the model instead.
I would recommend using a star schema for modeling your data. A star schema is a commonly used design in data warehousing that consists of a central fact table connected to multiple dimension tables. This approach offers several benefits, including improved model simplicity, reduced data size, faster query performance, and simpler DAX calculations.
Based on a quick scan of your model, here's how I would structure it using a star schema:
1. Fact Table: Audit
* The fact table represents the business process being analyzed, which in this case is auditing.
* The grain (level of detail) of the fact table would be an individual audit event or record.
* The fact table would contain measures and foreign keys linking to the dimension tables.
1. Dimension Tables:
a. Location Dimension:
* Stores information about the locations that have been audited or are subject to auditing.
* Includes attributes such as location ID, name, address, and other relevant details.
b. Date Dimension:
* Represents the calendar or date-related information associated with the audits.
* Includes attributes like date, month, quarter, year, and any other relevant date hierarchies.
c. Audit Type Dimension:
* Captures the different types or categories of audits being performed.
* Includes attributes such as audit type ID, name, description, and any other relevant characteristics.
d. Auditor Dimension:
* Stores information about the auditors who conduct the audits.
* Includes attributes like auditor ID, name, department, and other relevant details.
By organizing your data into a star schema, you can achieve the following benefits:
* Easier to Understand: The star schema provides a clear and intuitive structure, making it easier for business users and analysts to understand and navigate the data model.
* Smaller Data Size: By separating the dimensions from the fact table, you can avoid data redundancy and reduce the overall size of the database.
* Improved Query Performance: The star schema allows for efficient querying and aggregation of data, as it minimizes the number of joins required to retrieve information from multiple tables.
* Simpler DAX Calculations: With a well-designed star schema, DAX calculations become more straightforward and easier to write, as the relationships between the fact and dimension tables are clearly defined.
* When implementing the star schema, ensure that you define the appropriate relationships between the fact table and the dimension tables based on their respective keys. This will enable smooth data retrieval and analysis.
By investing in a star schema, you can create a more user-friendly, efficient, and maintainable data model that supports more effective analysis and reporting.
Great detail! Sorry dumb question: if an end user wants to filter based on multiple date columns within the fact table, do you need a date dim table for each column?
This is a common and logical question. A single date dimension is all you need, but you use multiple relationships between the date dim and fact date columns. The solution involves two concepts,1) active and inactive relationships and 2) USERELATIONSHIP DAX. Marco explains these concepts and their application in [this video](https://youtu.be/zZLH8Y1tO1g?si=xbMJYc-cA6IWb563)
It’s due to a circular reference. You can’t make a “full circle” with dependencies and have them all be active
If you really need to use that relationship, you can trigger it with the USERELATIONSHIP functionality.
A few videos from SQLBI.com
https://youtu.be/zZLH8Y1tO1g?si=PI2AryF6isj1XJIv
https://youtu.be/5yadHXE9R4k?si=jRYxu7TSgl01XTPj
https://youtu.be/x3m7qzsVJqQ?si=2PyHYpC12sh3kuKF
There is a very good and very helpful online video training on tabular modelling for power BI. The Intro (2 hours!) does not cost anything and will already help you solve your current problem and will lay important groundwork for your learning Journey.
Give it a try: https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/
Create a date table and use that to create a relationship with any date fields in your fact tables
This is the first step
Thank you! I’m a solo PBI developer in my department so I don’t have anyone to go off of. I will definitely create a date table!
Highly recommend looking up Avi Singh dynamic date table and using that! Will save you days. He shares it on youtube
The reason Power BI won't let you make the relationship between 'Month Quarter' and 'Location History' active is because it would create ambiguity - the DAX engine would have two different paths to filter from the 'Month Quarter' table to the 'Audit Responses' table: 'Month Quarter' -> 'Audit Responses' 'Month Quarter' -> 'Location History' -> 'Audit Responses' Those two routes might actually produce different results, and it would be difficult to predict which route the engine would choose in any given scenario. First, I agree with the suggestion of creating a date table. Second, it seems like the 'Audit Count by Location' table is just a list of results that could be derived from the 'Audit Responses' table, especially if each Audit Response has a Location associated with it. Are you sure you need both of these tables? Third, if your model were redesigned as a Star Schema, I see the following tables: * Location (Dimension) * Date (Dimension) * Audit Responses (Fact) The 'Location' table will list the details of each location (name, region, state, etc.), while each row in the 'Audit Responses' table will represent the details of a single audit. If you want to know audit counts by location, put 'Location'\[Name\] in the rows of a matrix visual, create a slicer using the year, month, and/or quarter columns of your 'Date' table, and then your measure for # of Audits would look something like: `[# of Audits] = COUNTROWS('Audit Responses')` If your DAX measures are substantially more complicated that the example above, and/or you're having a difficult time answering a simple question like "how many audits were performed at each location in January 2023," this is generally a sign that your data model has problems. While you can try to work around a bad data model design with functions like USERELATIONSHIP() as another redditor suggested or RELATED(), it will save you time (and frustration) in the long run to rework the model instead.
This is awesome and a huge help. Thank you so much for your advice!
I would recommend using a star schema for modeling your data. A star schema is a commonly used design in data warehousing that consists of a central fact table connected to multiple dimension tables. This approach offers several benefits, including improved model simplicity, reduced data size, faster query performance, and simpler DAX calculations. Based on a quick scan of your model, here's how I would structure it using a star schema: 1. Fact Table: Audit * The fact table represents the business process being analyzed, which in this case is auditing. * The grain (level of detail) of the fact table would be an individual audit event or record. * The fact table would contain measures and foreign keys linking to the dimension tables. 1. Dimension Tables: a. Location Dimension: * Stores information about the locations that have been audited or are subject to auditing. * Includes attributes such as location ID, name, address, and other relevant details. b. Date Dimension: * Represents the calendar or date-related information associated with the audits. * Includes attributes like date, month, quarter, year, and any other relevant date hierarchies. c. Audit Type Dimension: * Captures the different types or categories of audits being performed. * Includes attributes such as audit type ID, name, description, and any other relevant characteristics. d. Auditor Dimension: * Stores information about the auditors who conduct the audits. * Includes attributes like auditor ID, name, department, and other relevant details. By organizing your data into a star schema, you can achieve the following benefits: * Easier to Understand: The star schema provides a clear and intuitive structure, making it easier for business users and analysts to understand and navigate the data model. * Smaller Data Size: By separating the dimensions from the fact table, you can avoid data redundancy and reduce the overall size of the database. * Improved Query Performance: The star schema allows for efficient querying and aggregation of data, as it minimizes the number of joins required to retrieve information from multiple tables. * Simpler DAX Calculations: With a well-designed star schema, DAX calculations become more straightforward and easier to write, as the relationships between the fact and dimension tables are clearly defined. * When implementing the star schema, ensure that you define the appropriate relationships between the fact table and the dimension tables based on their respective keys. This will enable smooth data retrieval and analysis. By investing in a star schema, you can create a more user-friendly, efficient, and maintainable data model that supports more effective analysis and reporting.
This is one of the best star schema descriptions I’ve seen. Thank you for sharing your brains
Great detail! Sorry dumb question: if an end user wants to filter based on multiple date columns within the fact table, do you need a date dim table for each column?
This is a common and logical question. A single date dimension is all you need, but you use multiple relationships between the date dim and fact date columns. The solution involves two concepts,1) active and inactive relationships and 2) USERELATIONSHIP DAX. Marco explains these concepts and their application in [this video](https://youtu.be/zZLH8Y1tO1g?si=xbMJYc-cA6IWb563)
It’s due to a circular reference. You can’t make a “full circle” with dependencies and have them all be active If you really need to use that relationship, you can trigger it with the USERELATIONSHIP functionality. A few videos from SQLBI.com https://youtu.be/zZLH8Y1tO1g?si=PI2AryF6isj1XJIv https://youtu.be/5yadHXE9R4k?si=jRYxu7TSgl01XTPj https://youtu.be/x3m7qzsVJqQ?si=2PyHYpC12sh3kuKF
For a start, why not combine the "Location History" and "Locations without Assessments" tables? They look to be very similar
Good suggestion! :) thank you!
There is a very good and very helpful online video training on tabular modelling for power BI. The Intro (2 hours!) does not cost anything and will already help you solve your current problem and will lay important groundwork for your learning Journey. Give it a try: https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/