If a group of rows contains at least one of the filtered values, return the entire group of rows

[lpriceFTW]

I want to use DAX to select a group of rows containing a common value in field X, so long as there exists at least one instance/row of a filter value in field Y.

For example, take the following table:

Invoice No.	State	Amt
10001	TX	100
10001	WV	50
10001	PA	50
10002	WA	50
10002	VA	100
10003	IL	50
10004	TX	100

And the following filter:

Based on the filtered value ("TX"), the DAX/method will return all rows of each invoice containing at least one row with TX in the state field. Therefore, when the filter is applied, the returned data will be:

Invoice No.	State	Amt
10001	TX	100
10001	WV	50
10001	PA	50
10004	TX	100

Basically, since at least one rows of invoices 10001 and 10004 contained a value of "TX" in the state field, I want to return all the rows from those invoice.

I am trying to make it so this calculation changes based on the filtered value. So if the report user filtered to PA, TX, and WV, then the calculation would pull all the rows of invoice data of any invoice containing at least one row of any of the states that were filtered for.

This can involve calculated columns, measures, filter settings, etc.

Thanks

[bolfri · Accepted solution]

Step 1: Create a new table with states:

dim_state = GROUPBY('Sample';'Sample'[State])

Step 2: Create a measure Amount:

Amount =

var state_list = VALUES(dim_state[State])

var invoice_list = SUMMARIZE(FILTER(ALL('Sample');'Sample'[State] in state_list);'Sample'[Invoice No.])

return SUMX(

        FILTER('Sample';OR('Sample'[State] in state_list;'Sample'[Invoice No.] in invoice_list));

        'Sample'[Amt]

        )

state_list contains information about selected states

invoice_list contains information about all invoice number that matches the states

measure return number of amount column when the state is in state list or invoice number in invoice list

I hope this works for you 🙂

[lpriceFTW]

Did you create the measure within the Sample table?

Also is your filter pictured using the dim_state table or other data?

I tried your method and I am getting odd results:

[bolfri]

Invoice No. > 'Sample'[Invoice  No.] with the Don't Summarize option

State > 'Sample'State with the Don't Summarize option

Amount is that measure

I think you are using State from State table, thats why all the values are showing + Invoice number is without Dont Summarize option, what why you are seeing 20005 Invoice number that doesn't exist (its a sum of 10001 and 10004.)

[lpriceFTW]

Your suggestion to use "don't summarize" fixed it. Thank you for this!