0

Let's say I set my slicer value to N = 3 and Year = 2021, I would like to return a table that shows which products were in the Top 3 in the selected year 2021 but not in the prior year 2020.

So if the TOP 3 products in 2021 were Bananas, Oatmeal and Chocolate, and in 2020 were Bananas, Bread, and Coffee, the result should just be Oatmeal and Chocolate. In general, the results will vary for the user-selected values of N and Year.

I've written a DAX query that does exactly this with no problem. The problem is that I can't dynamically use it in Power BI:

``` Measure Top N SKU IN = 
VAR N = MAX('Popularity Slicer'[Value]) -- get max value on the popularity slicer
VAR YearSlicer = SELECTEDVALUE('L2 Top SKUs'[Year]) -- get sliced year

-- filter table on TOP N and selected Year
VAR SelectedYearTable = 
    CALCULATETABLE(
        'L2 Top SKUs',
        FILTER('L2 Top SKUs', 'L2 Top SKUs'[Popularity] <= N),
        FILTER('L2 Top SKUs', 'L2 Top SKUs'[Year] = YearSlicer) -- look at the previous year
    )

-- filter table on TOP N and prior Year
VAR PriorYearTable = 
CALCULATETABLE(
        'L2 Top SKUs', 
        FILTER('L2 Top SKUs', 'L2 Top SKUs'[Popularity] <= N),
        FILTER('L2 Top SKUs', 'L2 Top SKUs'[Year] = YearSlicer - 1 )
    )
    
-- get popularity measure
VAR SelectedYear = 
    SELECTCOLUMNS(
        SelectedYearTable,
        "Matnr", [Matnr],
        "Year", [Year],
        "Cosine", [Total Cosine Projection],
        "Popularity", RANKX(SelectedYearTable, [Total Cosine Projection])
        )
        
VAR PriorYear = 
SELECTCOLUMNS(
    PriorYearTable,
    "Matnr", [Matnr],
    "Year", [Year],
    "Cosine", [Total Cosine Projection],
    "Popularity", RANKX(PriorYearTable, [Total Cosine Projection])
    )
            
-- get the intersection  
VAR SKUs = EXCEPT(
    SELECTCOLUMNS(
        FILTER(SelectedYear, [Popularity] <= N),
        "SKU",
        [Matnr]
    ),
    SELECTCOLUMNS(
        FILTER(PriorYear, [Popularity] <= N),
        "SKU",
        [Matnr]
    )
)

RETURN
SKUs```

The measure won't return the results, SKUs because it contains multiple values. I can't compute a table, however, because SELECTEDVALUE only works in measures . . . Below is a screenshot of what the query should return. Can anyone help?

Screenshot of DAX query which returns new "Most Popular" products in 2021 for N = 20 and Year = 2021

EDIT: I've since tried taking the virtual column, SKUs, and converting this into an actual measure. I did this with the following code in DAX Studio and it worked perfectly (see attached photo).

The code asks the base table 'L2 Top SKUs' if any of it's parts numbers (Matnr) are in the virtual column SKUs and returns 1 if "yes" and 0 if "no". Then, to evaluate the measure, I add it as a column to the base table and filter for [Top] = 1, which gives the correct answer. When I go to do this in PowerBI it gives some 1's and some 0's but it's not correct and it is the same exact code:

MEASURE 'L2 Top SKUs'[Top] = If(FIRSTNONBLANK('L2 Top SKUs'[Matnr], 0) IN SKUs, 1, 0)

VAR Results = ADDCOLUMNS(
        SELECTCOLUMNS(
            'L2 Top SKUs',
            "Matnr", 'L2 Top SKUs'[Matnr]
        ),
        "Top", [Top]
    )

EVALUATE
DISTINCT(FILTER(Results, [Top] = 1))```


![Screenshot of DAX query which returns new "Most Popular" products in 2021 for N = 20 and Year = 2021][2]


  [1]: https://i.stack.imgur.com/WLAHQ.png
  [2]: https://i.stack.imgur.com/yt0bV.png
Maria Lentini
  • 1
  • 3
  • Where/how do you want to present your output? You can join all result value to one string by concatenatex https://dax.guide/concatenatex/ – msta42a Jan 15 '22 at 21:46
  • I haven't had any success with concatenatex, unfortunately. What I've since tried is taking the virtual column, SKUs, which is a list of parts in 2021 but not in 2020, and converting it into a measure. In DAX Studio this actually works perfectly. But when I create this measure in PowerBI and then create a table using it I get essentially all 1's, which is unfortunate. MEASURE 'L2 Top SKUs'[Top] = If(FIRSTNONBLANK('L2 Top SKUs'[Matnr], 0) IN SKUs, 1, 0) VAR Results = ADDCOLUMNS( SELECTCOLUMNS( 'L2 Top SKUs', "Matnr", 'L2 Top SKUs'[Matnr] ), "Top", [Top] ) – Maria Lentini Jan 16 '22 at 17:11

1 Answers1

0

I found a work around for what I was trying to do, which was to calculate a table that shows the change in a measure from a previous time period. The way to do this is to have two Date tables connected in series to the primary table (or tables) and to make the relationship between them inactive. I then used two different slicers (one corresponding to each of the Date tables) which I was able to use to compare two different periods of time. The two periods are user set in this case, which is not exactly what I was thinking (I wanted the time differential to be automatically set to 1 year), but it does job.

Here's how to build it: https://www.daxpatterns.com/comparing-different-time-periods/

Maria Lentini
  • 1
  • 3