Return an array of unique values in a measure/variable as a filter for further calculations

[ssingh_leecare]

I wish to return an array or a list of unique "PersonIDs" in a measure/variable within a measure.

And then use it as a filter for another measure/variable to display the results for only those personID's who were there in previous case.

[ssingh_leecare]

Hi @kunal15sep @Jihwan_Kim 
Thank you for your responses. I will explain the problem I have and what I am trying to achieve. These are the tables and connections I have right now.

FirstFormSaves has first saves for different forms for every personID, with the dates the forms were saved. So, for every personID, there's only one Form save of a certain type. 
This is connected to date table.

Person has all the personIDs.

Status is the table with personIDs, their status and the dates. 

I am using a timeline slicer. Assuming start date is A and End Date is B, I wish to return:

Get those personIDs which had status "XYZ" between A and B from table Status (Or Person since it is a 2-way relation).
Once we have these personIDs, get the total of all forms saved till B (Ignore start date A) only for those personIDs who we got from the statement above.

The personIDs we need have to be with status XYZ only between A and B. Forms saved should be everything till B, for only these personIDs.

I hope this clears the doubts, let me know if you need anything else.
Thanks in advance!

[ssingh_leecare]

Re: Return an array through a measure, and then us... - Microsoft Power BI Community
This was also posted by me and @amitchandak shared some insights, which may help.

Hi @amitchandak, this is a detailed explanation of what I am after.

Thanks in advance!

[Jihwan_Kim]

Hi,

I do not know how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below sample can provide some ideas on how to create a solution for your datamodel.

September 2022 qty > 15 IDs total qty: =
VAR _condition =
    FILTER (
        ADDCOLUMNS (
            ALL ( PersonID[PersonID] ),
            "@sepQTY",
                CALCULATE (
                    SUM ( Sales[Quantity] ),
                    EOMONTH ( Sales[Date], 0 ) = DATE ( 2022, 9, 30 )
                )
        ),
        [@sepQTY] > 15
    )
VAR _list =
    SUMMARIZE ( _condition, PersonID[PersonID] )
RETURN
    CALCULATE (
        SUM ( Sales[Quantity] ),
        KEEPFILTERS ( PersonID[PersonID] IN _list )
    )

[kunal15sep]

You can use VALUES(<TableNameOrColumnName>) for array of unique values in a column

[ssingh_leecare]

Hi @kunal15sep 
And how should I use that as a filter in the next statement, something like
var A = values(personID)
filter(personIDs IN A)

[kunal15sep]

@ssingh_leecare , If what I understand to be your requirement, you may consider using calculated column to LOOKUPVALUE and return 1 or 0 comparing from the previous case table. Then filtering the table view to get aggregated measures.

[ssingh_leecare]

In response to a calculated column, it has to be dynamic and change with the selection on date slicer, which won't be possible if I make a calculated column.