Filter on multiple Columns / Values - Data Consolidation - Sample Data

[Quakker]

Filter on multiple Columns / Values - Data Consolidation

‎06-06-2023 12:05 PM

Hi All,

i´m struggeling to create a Costcenter Report, where some kind of consolidation has to be done.

Attached you can see some sample data.

What i want to achieve is: If a User selects a Costcenter - he should see the Amount of costs.

If a User selects two Costcenter - there are some costs which are transferred between these 2.

For Example a user Filters on the Costcenter: 43960654 and Costcenter 43960552. 

He should see all rows, where the specific costcenter is in Column "COSTCENTER".

In Addition to this the user should see all other rows, where the COSTCENTER is blank, but the column "CONSOLIDATION" has both Costcenters included: in this case: 43960654-43960552 or 43960552-43960654 (all Rows with a red boarder).

I tried a lot but i am unable to create a measure and i´m even not sure if this can work?

I hope anyone can help me?

Best Regards!

COSTCENTER	AMOUNT	MONTH	SOURCEOBJECT	COSTELEMENT	CONSOLIDATION	HIERARCHY
43960654	92531,88	1	43960552	6600250000		TOTAL_COSTS
43960654	52374,15	1	43960551	6600250000		TOTAL_COSTS
43960654	406388,97	1		6600260000		TOTAL_COSTS
43960552	736	1	43960654	6520000001		TOTAL_COSTS
43960552	684,54	1	43960655	6520000016		TOTAL_COSTS
43960552	91111,46	1		4020110000		TOTAL_COSTS
	-92531,88	1	43960552	8888888888	43960654-43960552	TOTAL_COSTS
	92531,88	1	43960552	9999999999	43960654-43960552	CHARGES
	-52374,15	1	43960551	8888888888	43960654-43960551	TOTAL_COSTS
	52374,15	1	43960551	9999999999	43960654-43960551	CHARGES
	-736	1	43960654	8888888888	43960552-43960654	TOTAL_COSTS
	736	1	43960654	9999999999	43960552-43960654	CHARGES
	-684,54	1	43960655	8888888888	43960552-43960655	TOTAL_COSTS
	684,54	1	43960655	9999999999	43960552-43960655	CHARGES

[Anonymous · Accepted solution]

Hi @Quakker ,

I suggest you to try to create a measure to filter your visual.

Filter = 
VAR _SELECT = VALUES(DimCostcenter[COSTCENTER])
RETURN
IF(MAX('Table'[COSTCENTER]) IN _SELECT || MAX('Table'[SOURCEOBJECT]) IN _SELECT,1,0)

Add this measure into visual level filter and set it to show items when value =1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.