Power BI DAX Query Issue: Last X Items Filter Consideration

[codingenthu]

Hi,

I am currently facing an issue with a DAX query in PowerBI related to the "Last X Items" filter. The challenge is that the filter considers continuous values even if they are missing in the dataset. Let me illustrate with an example.

I have a dataset with the following serial values:
1
2
3
4
7
8
10

Here, Items 5, 6, and 9 are missing. When applying the "Last 3 Items" filter, PowerBI includes items 8, 9, and 10 as the last three, but in reality, Item 9 is not present in the data.

DAX Measures:

I've used the following DAX measures:

CostSumByItem =
VAR item = SELECTEDVALUE(CostCountByItem[Item])
VAR maxItem = CALCULATE(MAX(CostCountByItem[Item]), REMOVEFILTERS(CostCountByItem[Item]))
VAR selectedItemCount = SELECTEDVALUE(SelectedItemsCount[value])
VAR inWindow = IF(item > (maxItem-selectedItemCount ), TRUE(), FALSE())
RETURN IF(inWindow, SUM(CostCountByItem[Count]), BLANK())
-----------------------------------------------------------------------------------

SelectedItemsCount =
VAR firstseries = GENERATESERIES(2,4)
VAR secondseries = GENERATESERIES(5,20,5)
RETURN ADDCOLUMNS(
UNION(UNION(firstseries,secondseries), ROW("value", 10000)),
"Name",
IF([value]>100, "All", "Last " & [value])
)

Issue:

The problem arises when the Last X Items filter includes non-continuous values, leading to incorrect results.

Thanks in Advance for your help!

[VahidDM · Accepted solution]

Hi @codingenthu 

The issue you're facing is a common challenge when dealing with gaps in data series in Power BI using DAX. Your current approach calculates the last X items based on the assumption of a continuous series. However, as you have noticed, this does not work well when there are gaps in the data.

To solve this, you need to modify your DAX measure to account for gaps in the series. Instead of relying on the assumption that the last X items are continuous, you should identify the last X distinct items present in your dataset. Here's an approach to modify your CostSumByItem measure:

Determine the Last X Items Considering Gaps: You need a way to dynamically identify the last X items in your data. This can be achieved by creating a rank for each item based on its order in the dataset and then filtering out the top X items.

Calculate the Sum for the Identified Items: Once you have the last X items, you can proceed to calculate the sum for these items.

Here's a revised version of your CostSumByItem measure:

CostSumByItem Revised =
VAR selectedItemCount =
    SELECTEDVALUE ( SelectedItemsCount[value] )
VAR RankedItems =
    ADDCOLUMNS (
        SUMMARIZE ( CostCountByItem, CostCountByItem[Item] ),
        "Rank", RANKX ( ALL ( CostCountByItem ), CostCountByItem[Item],, DESC, DENSE )
    )
VAR FilteredItems =
    FILTER ( RankedItems, [Rank] <= selectedItemCount )
RETURN
    SUMX ( FilteredItems, CALCULATE ( SUM ( CostCountByItem[Count] ) ) )

In this revised measure:

RankedItems creates a rank for each distinct item in descending order.
FilteredItems filters the ranked items to keep only the top X items as per your selected item count.
The SUMX function then calculates the sum of Count for these filtered items

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube