cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## FIltering but no quite filtering

Hello everybody,

I need to create a report in power bi and I have this table:

 level 1 level 2 Amount M1 C1 10 M1 C2 20 M1 C3 30 M1 M1 5

in level 1 a market, in level 2 companies contained in the level 1 market, column 3 values. I am using first 2 columns (level 1 and level 2) as distinct filters.

I need this:

- when I select level 1 M1 without selecting nothing on level 2 , I need to get level 2 M1 value - 5;

- after selecting level 1 M1, when I select level 2 C1 I need to have C1 value - 10,

- if I select C1 and C2 , I want to get value 30 (C1+C2).

Can you help me with this please?

1 ACCEPTED SOLUTION
Helper III

Hii @Stelian

I made the following measure, and got the results that are there in the screenshots attached.
Please try it and let me know whether your problem is solved or not.
Measure Formula =

Slicer =
VAR Markets =
SELECTEDVALUE ( Marketcompany[Market] )
VAR Companies =
SELECTEDVALUE ( Marketcompany[Company] )
VAR Amt =
CALCULATE ( SUM ( Marketcompany[Amount] ), ALL ( Marketcompany[Market] ) )
VAR AmtMarket =
CALCULATE ( SUM ( Marketcompany[Amount] ), Marketcompany[Company] = Markets )
RETURN
SWITCH (
TRUE (),
AND (
ISFILTERED ( 'Marketcompany'[Market] ),
ISFILTERED ( Marketcompany[Company] )
), Amt,
ISFILTERED ( Marketcompany[Market] ), AmtMarket,
Amt
)

6 REPLIES 6
Helper I

Thank you.

Helper III

Happy to help..!!

Helper III

Hii @Stelian

I made the following measure, and got the results that are there in the screenshots attached.
Please try it and let me know whether your problem is solved or not.
Measure Formula =

Slicer =
VAR Markets =
SELECTEDVALUE ( Marketcompany[Market] )
VAR Companies =
SELECTEDVALUE ( Marketcompany[Company] )
VAR Amt =
CALCULATE ( SUM ( Marketcompany[Amount] ), ALL ( Marketcompany[Market] ) )
VAR AmtMarket =
CALCULATE ( SUM ( Marketcompany[Amount] ), Marketcompany[Company] = Markets )
RETURN
SWITCH (
TRUE (),
AND (
ISFILTERED ( 'Marketcompany'[Market] ),
ISFILTERED ( Marketcompany[Company] )
), Amt,
ISFILTERED ( Marketcompany[Market] ), AmtMarket,
Amt
)

Helper I

Thank you so much.

Super User

@Stelian , That should happen with 2 separate slicers. default power bi feature, can you share the issue screenshot

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers?tabs=powerbi-deskt...

Helper I

The company structure:

 market company M1 C1 M1 C2 M1 C3 M1 M1 M2 C4 M2 C5 M2 C6 M2 M2

The figures:

 company Amount C1 10 C2 20 C3 30 M1 5 C4 23 C5 54 C6 65 M2 12

The filters:

 market company M1 C1 M2 C2 C3 M1 C4 C5 C6 M2

When I select only market M1 I need to get only M1 value from company level: 5.

When I select market market M1 and company C1, i need to get only C1 value 10.

When I select market market M1 and companies C1 and C2, i need to get C1+C2 value 30.

I've created new tables one for market and one for company and connected with first table, I've tried all types of connections, hoping that when I select only market M1 with no selections for companies I will get the value for second level M1 but I allways get the sum of M1, C1, C2 and C3.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors