Central Tendencies

Tag: BI

Business Intelligence

  • Maven Analytics – Data Drills #5 – Movie Metrics – DAX Query Solution

    This is my DAX Query solution to Maven Analytics Data Drill #5 – Movie Metrics.

    The challenge is to create new columns in the USERS table based on data given in the USER ACTIVITY table. The new columns to be added are :

    • Date from the first movie they finished
    • Name of the first movie they finished
    • Date from the last movie they finished
    • Name of the last movie they finished
    • Movies started
    • Movies finished

    Note: You might notice, I didn’t follow the instructions to the T . I did not create columns in the USERS table. Instead, I decided to solve this using a series of measures. This approach was natural for me, as I new the final query would produce the final table. Also, I completely missed the point where it should have been solved in the USERS table !

    First we create the Count Movies metric which as the name implies, gives as a count of the movies. This may help us with troubleshooting DAX code.

    DEFINE
    MEASURE 'Box'[Count_movies] =
            CALCULATE(
   COUNTROWS(    VALUES(
    'activity'[movie_name]
))

    Then we find the first movie the user has finished by getting the minimum date using the MIN() function, followed by the CALCULATE() function. The CALCULATE() function will force a context transition for each user. We then filter the activity table to only query finished movies.

    MEASURE 'Box'[First_Date] = 
        CALCULATE(
            MIN('activity'[date]),
            'activity'[finished] = 1
        )

    For First Name , we need to filter the table, by the minimum date and the finished flag. We access the min_date by referring to the first_date measure and store it in a variable. Thereafter we use it to filter the table and get the movie name by using the MAX() function.

    MEASURE 'Box'[First_Name] = 
        VAR min_date = [First_Date]
        RETURN
            CALCULATE(
                MAX('activity'[movie_name]),
                'activity'[date] = min_date,
                'activity'[finished] = 1
            )

    For Last Date and Last Name, the logic is similar to First Date and First Name but we change the MIN() to a MAX() function.

    MEASURE 'Box'[Last_Date] = 
        CALCULATE(
            MAX('activity'[date]),
            'activity'[finished] = 1
            
        )
        MEASURE 'Box'[Last_Name] = 
        VAR max_date = [Last_Date]
        RETURN
            CALCULATE(
                MAX('activity'[movie_name]),
                'activity'[date] = max_date,
                'activity'[finished] = 1
            )

    For the final two measures, Movies Started and Movies Finished, we use the COUNTROWS() function to count the rows in the table while filtering it by 0 or 1 respectively, while wrapping it around a calculate function.

    MEASURE 'Box'[Movies_Started] = 
                CALCULATE(
                    COUNTROWS('activity'),
                    'activity'[finished] = 0
                )
MEASURE 'Box'[Movies_Finished] = 
                CALCULATE(
                    COUNTROWS('activity'),
                    'activity'[finished] = 1
                )

    Finally, after creating the measures, we can create the table and pass the measures as columns in the USER ACTIVITY table. We filter the table using the FILTER() function for the “Fight Club” movie in the last name column, to get users who watched the movie last. To get the final answer, we count the rows in the table using COUNTROWS().

    (Note: If we were to pass them in the USERS table we would most likely have to use the RELATEDTABLE() function to refer to the columns in the other table.)

    EVALUATE
VAR T =
SUMMARIZECOLUMNS(
    'activity'[user_id],
    "Count_movies", [Count_movies],
    "First_Date", [First_Date],
    "First_Name", [First_Name],
    "Last_Date", [Last_Date],
    "Last_Name", [Last_Name],
    "Movies_Started", [Movies_Started],
    "Movies_Finished", [Movies_Finished]
)
 VAR Y = FILTER(T, [Last_Name] = "Fight Club")
RETURN
	// T
	// V
   { COUNTROWS( Y ) }
ORDER BY
    [Count_movies] DESC

    Summarised Table

    Final Table

    Final Count

    Submitting the answer

    That’s all for today ! 🤗👋🏾

    Video Tutorial

  • Maven Analytics – Data Drills #4 – Spot the Sale – DAX Query Solution

    This is my DAX Query solution to Maven Analytics Data Drill #4 – Spot the Sale.

    The challenge is to join each transaction to the promotion active on its order date, and find out how many orders were placed outside of the promotional period.

    Disclaimer: This solution is based Aaron Perry’s DAX solution, an instructor from Maven Analytics.

    For this challenge, my initial impulse was to unpivot the promotions table, so that the dates can all fall under one column. Once we have them under one column, then we can find a way to build a table that contains the correct promotion for each date, then perform a left join on the orders table.

    Suggested Solution Example:

    Promotions table

    promo_idpromo_nameDates
    BF_2023Black Friday24/11/2023
    BF_2023Black Friday25/11/2023
    BF_2023Black Friday26/11/2023
    BF_2023Black Friday27/11/2023
    BF_2023Black Friday28/11/2023
    BF_2023Black Friday29/11/2023

    Orders Table after left join

    order_idorder_dateorder_quantitypromo_id
    61024-Nov-232BF_2023
    61125-Nov-235BF_2023
    61225-Nov-233BF_2023
    61327-Nov-231BF_2023
    61428-Nov-232BF_2023
    61529-Nov-235BF_2023
    61629-Nov-232BF_2023

    However, I feel, the challenge involves how to accurately and dynamically expand the promotions table for each promotion date.

    This approach, although may work, is still an SQL way of thinking. The better approach is to think in DAX. The better way is to create a new column that would check for each row, in the column if it belongs in the promotion range, then return the promotion name.

    DEFINE
    COLUMN 'orders'[promo_ids] = 
        VAR d = 'orders'[order_date]
    RETURN
        CALCULATE(
            MAX('promotions'[promo_id]),
            FILTER(
                ALL('promotions'),
                'promotions'[start_date] <= d &&
                d <= 'promotions'[end_date]
            )
        )
EVALUATE
{
    COUNTROWS(
        FILTER( 'orders', ISBLANK( 'orders'[promo_ids] ) )
    )
}

    First, the current date being evaluated , in the row context and then saved in the variable d. We then remove all of the filters from the promotions table using ALL(), then explicitly filter the promotions table again, based on the condition that the order date is between the start date and end date. once this is true it returns a one row table (since there is no overlap in promotions). Thereafter we get the individual value using max and wrap it around calculate, in order to force the filter.

    Once we have the column, we then query the orders table for blank promo ids.

    Video Walkthrough

    That’s all for today! 👋🏾😁

  • What is Business Intelligence ?

    What is Business Intelligence ?

    Photo by Christina Morillo from Pexels.

    Companies, big and small, often face many challenges when operating in their business environments. Challenges that require informed decision-making in order to address them correctly. For example, A firm may face declining sales in a certain region, however, this may not be apparent due to a lack of clear visibility; or a competitor may have raised their prices on a premium product, causing a business to miss potential revenue; or increased delays in transportation in its supply chain.

    Enter Business Intelligence, usually abbreviated as B.I

    Business Intelligence is the process of using data to answer business questions in order to produce a call to action.

    Management, Team leaders and team members, need to have sufficient information before taking any course of action, otherwise they risk negatively impacting the business. Luckily, in today’s world, businesses generate large amounts of data (Big Data), which can be used to gain insights before making a decision.

    The B.I process involves identifying the business requirements, data sources, transforming said data, discovering insights, visualising, and finally presenting and sharing the findings with stakeholders, in order for them to take action.

    B.I often falls under Descriptive and Diagnostic analytics – B.I helps us know “What happened ?” and “Why did it happen ?”. In that sense, it is a backward-looking or historical form of analytics.

    It is often real-time or near real time which allows for stakeholders to react quickly to any changes in the internal or external environment.

    HR Attrition Dashboard by Pradeep Kumar. Interactive version

    Some benefits of B.I include

    • Improved reaction time towards business events.
    • Proactive (preventative) measures before business events happen.
    • Performance overview of the business (usually in the form of KPI’s).
    • Enhanced operational efficiencies.

    In order for companies to maximise the benefits of business intelligence, they need to have a well thought out B.I Strategy which may fall under a companies overall (Big) data strategy.

Disclaimer - Privacy Policy