Filtering columns of a dynamic matrix

[Anonymous]

Dear Community,

I have the following challenge I cannot master:

My fact table contains transactions, defined by their idividual transaction IDs. The table also contains the columns Program and process to define which program and which process within that program the transaction belongs to.

Each transaction can has a number of referenes which are listed in a related dimension table. Each reference has the related transacion ID as well as a a reference type and a value. The refernce types are specific to the certain processes the transaction belongs to.

When I create a matrix with the transactions IDs as rows, the refrence types as columns and the reference values as values, the number of columns differ depenting on the programs/processes I filter the matrix via slicers. As new reference types can be added in the source, I cannot predict the columns in advance.

I now want to user to be able to filter the matrix, so that only these transactions are shown that contain the set of reference values the user defined.

My attempt for this was to create a sencond matrix with only the refence types and values that do not slice the main matrix, to create a form of selector. The SUMMARIZE funtion would then let me collect all selected values for all reference type. Unfortunatly I cannot find a solution to write a dynamic function to filter the transactions with these information as there is no such thing like the INDIRECT funtion in excel.

So my question is: Is there any option in DAX to write such a dynamic function? Or is there another way to filter a dynamic matrix as descripted?

Thanks in advance for your input.

[Anonymous]

Hi @Greg_Deckler 

thanks for your answer.

Let's say my fact table looks like this:

Transaction ID	Program	Process
123456	Program A	Process A-1
123457	Program B	Process B-3
123458	Program A	Process A-2
123459	Program B	Process B-1
123460	Program C	Process C-1
123461	Program C	Process C-1
123462	Program C	Prcoess C-3
123463	Program B	Process B-2
123464	Program A	Process A-2
123465	Program A	Process A-1

and my dimension table looks like this:

Reference ID	Transaction ID	Reference type	Reference value
r456789	123456	customer_ID	c-abc
r456790	123456	reason_for_calling	complaint
r456791	123456	solution	10% discount
r456792	123457	order_nr	ON-4587
r456793	123457	type_of_change	cancellation
r456794	123458	customer_ID	c-xyz
r456795	123458	invoice_nr	INV-896
r456796	123459	order_nr	ON-3689
r456797	123460	department	Sales
r456798	123460	item_categorie	IT accessoiries
r456799	123460	item	keyboard
r456800	123460	amount	10
r456801	123461	department	Customer Support
r456802	123461	item_categorie	office supplies
r456803	123461	item	writing pad
r456804	123461	amount	150
r456805	123462	department	Sales
r456806	123462	parking_lot	A10
r456807	123462	day	09/19/2023
r456808	123463	order_nr	ON-3689
r456809	123464	customer_ID	c-abc
r456810	123464	invoice_nr	INV-748
r456811	123465	customer_ID	c-xyz
r456812	123465	reason_for_calling	question regarding order
r456813	123465	solution	answer given

a matrix with the Transactions IDs as rows and Reference types as columns would look like this:

Transaction ID

customer_ID

reason_for_calling

solution

order_nr

type_of_change

invoice_nr

department

item_categorie

item

amount

parking_lot

day

123456

c-abc

complaint

10% discount

123457

ON-4587

cancellation

123458

c-xyz

INV-896

123459

ON-3689

123460

Sales

IT accessoiries

keyboard

10

123461

Customer Support

office supplies

writing pad

150

123462

Sales

A10

09/19/2023

123463

ON-3689

123464

c-abc

INV-748

123465

c-xyz

question regarding order

answer given

I put slicers on the site to filter by Program and/or Process. So lets say the user slices to only Process "A-1", the matrix would be:

Transaction ID	customer_ID	reason_for_calling	solution
123456	c-abc	complaint	10% discount
123465	c-xyz	question regarding order	answer given

I'm searching for a solution for the user to filter this matrix even further by column. For example in this case the user only wants to see all transaction for the customer ID "c-abc", or even a combination of costumer_ID = "c-abc" and reason_for_calling = "complaint".
I would like the matrix then to show all rows that match this condition including all columns for these rows (also the ones not filtered by the user).

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.