Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II
Helper II

Filter on multiple Columns / Values - Data Consolidation

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!


Helper II
Helper II

Hi Xiaoxin,

unfortunately i was still not able to create the measure we need.

I still doubt that it is even possible 😕

Additionally i could not upload any sample figures within this thread - i just received errors.

Therefore i´ve recreated the thread and posted the sample data insight.

We are still looking for a solution and i really hope that you can help.

Thanks already and best regards.

-- Here is the link with the sample data --

Filter on multiple Columns / Values - Data Consoli... - Microsoft Fabric Community

Helper II
Helper II

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
Helper II
Helper II

Hi all,

unfortunately i was still not able to find a solution.

Maybe it´s even not possible to create such a measure.

I still hope that someone can help us for this topic.

I´ve uploaded some sample data.

Best Regards

4396065492531,881439605526600250000 TOTAL_COSTS
4396065452374,151439605516600250000 TOTAL_COSTS
43960654406388,971 6600260000 TOTAL_COSTS
439605527361439606546520000001 TOTAL_COSTS
43960552684,541439606556520000016 TOTAL_COSTS
4396055291111,461 4020110000 TOTAL_COSTS


Community Support
Community Support

HI @Quakker,

You can try to use the following measure formula to get the different based on current COSTCENTER group:

formula =
VAR currCostcenter =
    IF (
        currCostcenter = BLANK (),
        SUM ( Table[Amount] ),
        VAR _path =
            SUBSTITUTE ( currCostcenter, "-", "|" )
            CALCULATE (
                SUM ( Table[Amount] ),
                FILTER ( 'Table', Table[SOURCEOBJECT] & "" = PATHITEM ( _path, 1 ) )
                - CALCULATE (
                    SUM ( Table[Amount] ),
                    FILTER ( 'Table', Table[SOURCEOBJECT] & "" = PATHITEM ( _path, 2 ) )

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

i just tried your sultion but it doesn´t seem to work.


Above you can see the results within Power BI. There are Costs about 643.827.

My expectation is that there are TOTAL Costs about 550.560 and CHARGES about 93.267




Do you know whats wrong?
Best regards and already thanks for your help!

Hi @Quakker,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  


Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

we were still not able to find a solution.

Currently we are not sure if it is even possible to create such a measure.

I´ve attached some sample data. Unfortunatley it was not possible to upload it as a table.

Already thanks for your help and best regards!



Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.