topic Re: Return an array of unique values in a measure/variable as a filter for further calculations in DAX Commands and Tips

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

ssingh_leecare — Wed, 09 Nov 2022 04:52:02 GMT

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.

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

kunal15sep — Wed, 09 Nov 2022 05:04:36 GMT

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

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

ssingh_leecare — Wed, 09 Nov 2022 05:07:56 GMT

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)

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

Jihwan_Kim — Wed, 09 Nov 2022 05:10:31 GMT

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 ) )

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

kunal15sep — Wed, 09 Nov 2022 05:16:56 GMT

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.

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

ssingh_leecare — Wed, 09 Nov 2022 05:32:13 GMT

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!

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

ssingh_leecare — Wed, 09 Nov 2022 05:42:53 GMT

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!

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

ssingh_leecare — Wed, 09 Nov 2022 05:48:25 GMT

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.