Thread

Dynamic Report Data Handler

Learn how to dynamically populate a report using daily CSV files based on a selected date, defaulting to the most recent date while allowing alternate selections. Includes M query adjustments and calculated columns in Power Query.

Dynamic Report Data Handler

Description

Content

Dynamic Report Data Handler
DAX Measures Refinement for Ranking Recent Games

Dynamic Report Data Handler

Tool: Tools Advisor

Created: 04/28/2024

Prompt

I am trying to combine two different problems into a single solution but am unable to get to where I need to be.

The problem is we have daily CSV files from which we want to populate our report based on the selected date from a slicer. Upon opening the report, it should always default to the most recent file (which should typically be today), but the user should have the ability to select prior dates so they can view the data from that period of time.

I am using a date parameter in power query bound to the Date modified column of the table of CSV files. Thanks to the many resources I found, I have it working so that when a Date modified is selected from the slicer, the parameter is updated accordingly and the data from that day’s CSV file is used to populate the visuals.

The issue I am having is when I try to implement “today” logic into my slicer. Instead of using the Date modified column from my table, I created a calculated column which determines if the Date modified is today and displays as such, otherwise displays the date from the Date modified column. This is the only way I know to have the report always open to today (the actual requirement is to open to the latest date but this should suffice):

_Today = IF(Data_Lake_Files[Date modified] = TODAY(), “Today”, Data_Lake_Files[Date modified] & “”)

When I use this new column in my slicer instead of the Date modified, the Date modified value from the bound column does not pass to my parameter. I have a visual which shows just the Date modified and I can see it correctly gets updated based on the value I select in my slicer, but it doesn’t seem to pass to the bound parameter. Therefore, my data never updates.

I am looking for a solution as to how I can get both of these features to work together: dynamic M query parameters and defaulting report to today.

Problem Analysis

The user is facing challenges in dynamically populating a report with data from daily CSV files based on a selected date from a slicer. They have successfully implemented a date parameter in Power Query bound to the "Date modified" column but are struggling to incorporate the logic for defaulting the report to the most recent date (typically today) while allowing users to select prior dates.

Strategic Solution Development

Use the existing date parameter to filter data based on the selected date.
For defaulting the report to today, create a separate calculated column to display "Today" if the date is today, otherwise, show the actual date.
To ensure that this default date passes correctly to the date parameter, you may need to modify the M query logic slightly.

Comprehensive Advice

Keep the date parameter bound to the "Date modified" column for filtering the data based on the user-selected date.

Create a calculated column in your Power Query editor using the following logic:

_Today = IF(Data_Lake_Files[Date modified] = TODAY(), "Today", Text.From(Data_Lake_Files[Date modified]))

Adjust the logic in your M query parameter to consider the "_Today" column for the default date. Ensure that this new logic correctly passes the selected date (either from slicer or "_Today" column) to the parameter for filtering the data.

Code-Based Solution

Here is an example of how you might adjust the M query for the parameter to incorporate the default logic using the "_Today" column:

let
    Source = ...,  // Your existing code
    DefaultDate = if Data_Lake_Files[_Today]= "Today" then Date.From(DateTime.LocalNow()) else Data_Lake_Files[Date modified]{0},  // Logic to determine default date
    filteredData = Table.SelectRows(Source, each [Date modified] = DefaultDate)  // Filter the data based on the default date
in
    filteredData

Detailed Explanations

By incorporating the logic to check if the selected date is "Today" in your calculated column and adjusting your M query to use this column for the default date, you should be able to combine the dynamic parameter functionality with defaulting the report to today effectively. Ensure that the parameter is updated based on the selected date or the "_Today" column to filter the data correctly.

DAX Measures Refinement for Ranking Recent Games

Tool: Tools Advisor

Created: 04/28/2024

Prompt

I am trying to analyze video game dataset, analyzing data from the 'Table1 ’ table. This table contains information about various video games, including their hitrate, game type, winrate, release date, and other details. Some game names may be repeated in the table. i want to create a combined DAX measure named ‘CombinedDAX’ that calculates the rank of the most recent games where the ‘MinDateFlag’ is 1.

Additionally, MinDateFlag represents a flag indicating the minimum release date for each game, even if the game name is repeated in the table. It is calculated
based on the release date of each game. If the release date of a game matches the minimum release date for that game, the MinDateFlag is set to 1; otherwise, it is set to 0.

I tried this its not working please help, attached a sample file

CombinedDAX =
VAR MinDateFlag =
IF(
‘Dim Game’[Release Date] = CALCULATE(MIN(‘Dim Game’[Release Date]), ALLEXCEPT(‘Dim Game’, ‘Dim Game’[Game Name])),
1,
0
)
VAR RecentGamesRank =
IF(
MinDateFlag = 1,
RANKX(
FILTER(‘Dim Game’, MinDateFlag = 1),
‘Dim Game’[Release Date],
,
DESC,
Dense
),
BLANK()
)
RETURN
RecentGamesRank

Problem Analysis

The user wants to create a DAX measure named 'CombinedDAX' that calculates the rank of the most recent games with MinDateFlag set to 1.
MinDateFlag defines whether a game's release date is the minimum for that game.
The user provided DAX measures but encountered issues in the calculation.

Strategic Solution Development

Adjust the DAX measure 'CombinedDAX' to properly calculate the MinDateFlag based on the conditions specified.
Modify the DAX measure 'RecentGamesRank' to calculate the rank of the most recent games with MinDateFlag as 1.

Comprehensive Advice

Create 'CombinedDAX' to correctly calculate MinDateFlag using the correct logic.
Refactor 'RecentGamesRank' to ensure it calculates the rank of the most recent games where MinDateFlag is 1.
Test the measures with sample data to verify the correctness of the calculations.
Consider using DAX functions like RANKX and FILTER to achieve the desired results efficiently.

Code-Based Solutions

CombinedDAX = 
VAR MinDateFlag =
IF(
    'Dim Game'[Release Date] = CALCULATE(MIN('Dim Game'[Release Date]), ALLEXCEPT('Dim Game', 'Dim Game'[Game Name])),
    1,
    0
)
RETURN
MinDateFlag

RecentGamesRank = 
VAR RecentGames =
FILTER('Dim Game', [MinDateFlag] = 1)
RETURN
IF (
    [MinDateFlag] = 1,
    RANKX(
        RecentGames,
        'Dim Game'[Release Date],
        ,
        DESC,
        Dense
    ),
    BLANK()
)

Detailed Explanations

The first DAX measure calculates MinDateFlag accurately by comparing each game's release date with the minimum release date for that game.
The second DAX measure refines the calculation by providing the rank of the most recent games where MinDateFlag equals 1.
Testing these measures with sample data will ensure that the ranking of the most recent games with the MinDateFlag set to 1 is computed correctly, addressing the user's requirement effectively.