Inside a measure - make a list of ID's from Summarize columns and use list to filter a base table

[ShellyPC]

Hi, I have a base table “Items” that looks like:

ItemId	PersonId	Spent	Year	YearAge
1	100	1000	2024	0
2	100	500	2022	2
3	100	200	2021	3
4	100	1200	2015	9
5	101	800	2023	1
6	101	400	2012	12
7	102	200	2023	1
8	102	100	2022	2
9	102	100	2022	2
10	102	100	2012	12
11	103	500	2014	10
12	103	600	2012	12
13	103	200	2012	12

We want to allow the user to choose a YearAge and a number of items.

We want to show the total spent by all People on ‘N’ or more items in the last ‘X’ years.

We also want to see the total spent in all years by the people who spent on ‘N’ or more items in the last ‘X’ years.

N and X are both values that the user selects on the same tab as the visual.  I created a Parameter for all possible YearAge values and supplied a slicer for the user to select an age ‘X’.

I created a parameter for Number of items ‘N’ and it is simply a number from 1 to 20 also selectable from a slicer.

So for instance if the user selected 3 items in the last 5 years (year age < = 5) our total would be calculated as follows:

Person 100 has spent on 3 items where yearage  < = 5  :Total spent = 1700

Person 101 has spent on only 1 item in the last 5 years so will not be counted

Person 102 has spent on 3 items where yearage  < = 5  :Total spent = 400

Person 103 has spent on 0 items in the last 5 years so will not be counted

Thus the total spent in the last 5 years is 2100.

This total is easy to get (and is working) using the following measure:

SpentInSelectedYears =

var FFYears = [Selected FF Years]

var FFItems = [Selected FF Items]

var SummaryTable = SUMMARIZECOLUMNS(Items[PersonId],

    FILTER(Items, Items[YearAge] <= FFYears),

    "CountItems", DISTINCTCOUNT(Items[ItemId]),

    "TotalSpent", SUM(Items[Spent]))

Var MCount = SUMX(FILTER(SummaryTable, [CountItems] >= FFItems), [TotalSpent])

RETURN Mcount

However the problem arises when I want to create a measure for the total ever spent by the same group of people. 

In other words, I would be looking for a total as follows:

Person 100:         Total spent = 2900

Person 101 has spent on only 1 item in the last 5 years so will not be counted

Person 102:         Total spent = 500

Person 103 has spent on 0 items in the last 5 years so will not be counted

Thus the total spent in all years is 3400.

I would (I think) first have to extract a list of people from the SummaryTable where [CountItems] >= FFItems.  Then use this list to filter the base table (Items) and get a sum of Spent (for all Years).  This is where I am now spinning my wheels – I am having trouble figuring out what the Dax needs to look like in this new measure.  How do I filter another table by the results of a SummarizeColumns expression inside a Measure.

It must be done in a measure because it needs to change as the user changes the parameter selection.

I have tried a number of things but they all fail.  It seems I do not know how to filter by PersonId in SummaryTable.

I thought the following would work:

SpentInAllYears =

var FFYears = [Selected FF Years]

var FFItems = [Selected FF Items]

var SummaryTable = SUMMARIZECOLUMNS(Items[PersonId],

    FILTER(Items, Items[YearAge] <= FFYears),

    "CountItems", DISTINCTCOUNT(Items[ItemId]))

var FFMembers = CALCULATETABLE(

    Values(SummaryTable[PersonID]),

    FILTER(SummaryTable, [CountItems] >= FFItems))

RETURN Calculate(SUM(Items[Spent]), FILTER(Items, Items[PersonId] in FFMembers))

This tells me that it can not find table ‘SummaryTable’.  I tried leaving out the table name:

Values([PersonID])

But then it can not find PersonID

I would be gratefull if somebody could suggest a way around this issue.

[DataInsights · Accepted solution]

@ShellyPC,

Try these measures:

Total Spent = SUM ( Items[Spent] )

Spent In All Years = 
VAR FFYears = [Selected FF Years]
VAR FFItems = [Selected FF Items]
VAR FilterYearAge =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Items[PersonId] ),
            "@CountItems", CALCULATE ( DISTINCTCOUNT ( Items[ItemId] ) )
        ),
        Items[YearAge] <= FFYears
    )
VAR FilterItemCount =
    FILTER ( FilterYearAge, [@CountItems] >= FFItems )
VAR PeopleWhoQualify =
    SELECTCOLUMNS ( FilterItemCount, "PersonId", Items[PersonId] )
VAR Result =
    CALCULATE ( [Total Spent], PeopleWhoQualify )
RETURN
    Result

[ShellyPC]

Hi DataInsights

Thank you for your reply.  However, it has the same issue as my original code.

The thing that gets me is that Power BI does not flag any error, but it returns Blank.  To check if there was anything in the various tables, I returned COUNTROWS(FilterItemCount).  This returns blank.  I assume that the Filter command will only work with a Base table and will not work with the variable that contains a table.  This is essentially the same as my original problem.

Also the Result variable is Calculate(A single number, List of people) - not sure how that is supposed to work.

Displaying what I need in a table or Matrix visual is easy and I can see the totals in the visual, same as what you show.  But I need to reproduce that total into a single value so i can use it in other calculations.

The First table (FilterYearAge) does have rows in it.  I can return a ROWCOUNT for it and it is what I expect.

Is there another table function that may work with the Calculated table?

[DataInsights]

@ShellyPC,

Could you clarify the expected result? Keep in mind that "calculated table" refers to a physical table created via DAX, whereas a table variable exists inside a DAX expression. A calculated table won't recognize user filters/slicers. The FILTER function accepts a table variable as illustrated in my variable FilterItemCount. If your model returns rows in FilterYearAge but not FilterItemCount, verify the variable FFItems. Are you able to share your sanitized pbix via one of the file services such as OneDrive?

[ShellyPC]

Hi DataInsights

I do appologize,  dont know what i did when duplicating your original answer.  I threw my working away and started again.  

It works perfectly, thank you so much!  I definitely prefer your logic flow to my original.

(And now that I see it working - the result does make sense to me - I understand why it works)

Thanks

[DataInsights]

@ShellyPC

Glad to hear it works!