Understanding the Problem Statement

The question posed in the Stack Overflow post revolves around Power BI, a business analytics service by Microsoft. The user has three tables: user, article, and views. The relationship between these tables is as follows:

• The user table contains information about users.
• The article table contains information about articles.
• The views table contains records of which articles are viewed by each user.

The goal is to display a list of articles that have not been viewed by any user. However, the current implementation does not yield accurate results due to an unexpected behavior where certain articles are not included in the view data based solely on their relationship with users who have already checked them.

Understanding Power BI’s Display Items

In Power BI, “display items” refer to the components of a dataset that can be visualized or used for calculations. When creating reports in Power BI, you typically connect it to a database or data source using OLE DB provider for SQL Server or other compatible databases.

When you create relationships between tables, Power BI uses those relationships to display data in a meaningful way and perform calculations based on the related columns. However, this can sometimes lead to unexpected behaviors if not properly understood.

The Problem with Display Items

In this case, there seems to be an issue where articles that are directly linked to users who have checked them at least once are missing from the list of unviewed articles. This problem occurs because Power BI does not always update display items based solely on changes in related tables. Instead, it often uses pre-existing relationships and data to calculate values for new datasets.

Solution Strategy

To solve this issue, we need to first identify the correct relationship between users who have checked an article at least once and those articles themselves. We will then leverage Power BI’s calculation features to find the number of unviewed articles by considering all possible combinations of related data.

Step 1: Identify Correct Relationships Between Articles and Users

To begin, we must establish accurate relationships between article and user. In this case, it seems that there is no direct relationship specified. Therefore, Power BI might be using a “contains” relationship for articles, where any user’s check mark on an article indicates the article has been viewed.

However, based on the question, we need to make sure this assumption is correct and consider whether we should instead use a many-to-many relationship for users who have checked multiple articles. We can achieve this by using Power BI’s “Many-To-Many” table relationship feature to establish a separate relation that connects each user with any articles they have checked.

Step 2: Establish Many-to-Many Relationship Between Users and Articles

To correctly link article data to users who have viewed them, we need to create a new “many-to-many” table in Power BI. This new table will contain two columns: one for each user ID (for those who have checked an article) and another column for the corresponding article ID.

We can use Power BI’s table relationships feature to link this many-to-many table with both user and article tables, ensuring that we capture all instances where a single user has viewed multiple articles or any single article is checked by one or more users.

Step 3: Update Display Items

Once the relationship between user, article, and the new “many-to-many” table is established, we should update display items in Power BI to accurately reflect this data. This includes ensuring that our calculations are based on all possible combinations of related articles and users who have checked them.

Step 4: Calculate Unviewed Articles

With the corrected relationships in place, we can use a combination of DAX formulas and Power BI’s calculation features to calculate the number of unviewed articles. This involves using measures that sum up the total count of articles minus those that are marked as viewed by any user.

// Calculate Total Number of Articles
Total Articles = 
    COUNTX( 
        All Articles, 
        ARTICLEID 
    )

// Measure to Calculate Unviewed Articles
Unviewed Articles = 
    CALCULATE(
        COUNTX(
            FILTER(
                All Articles,
                NOT IN (
                    RELATEDTABLE(
                        Many To Many Articles Checked by Users,
                        User ID
                    )
                )
            ),
            ARTICLEID
        )
    )

Step 5: Visualize Unviewed Articles

Finally, we will use a visualization in Power BI to display the list of unviewed articles. This can be achieved using various visualization types such as a bar chart or line graph.

// Visualization for Unviewed Articles
Unviewed Articles Bar Chart = 
    BAR(
        Visualize( 
            Calculate (
                SUMX(
                    FILTER(
                        'Table Name',
                        'Unviewed Articles' > 0
                    ),
                    'Unviewed Articles'
                )
            ),
            "Articles Count"
        ),
        'Article Name', 
        Color.Purple
    )

Conclusion

Display items in Power BI are crucial for understanding data and performing calculations. However, the behavior of these display items can sometimes lead to unexpected outcomes if not managed correctly.

By establishing a many-to-many relationship between users who have checked articles and the article table, we ensure that our dataset accurately reflects all possible interactions between users and articles in Power BI. With this understanding and through strategic use of DAX formulas, calculations, and visualizations, we can effectively find and display the list of unviewed articles for any given set of data.

References

• Microsoft Power BI Documentation
• Stack Overflow - Power BI Query Issue

Last modified on 2023-12-30