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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
octomore
New Member

Use filters for specific colums in matrix

Hi all,

I'm a begginer with Power BI , and i'm unable to use filters properly.

I have 1 main table

Month / Year / Department / QT sold.

 

I'd like to compare Qt sold during 2 choosen year ( Current & Refferent).

There is no rule to calculate one of the year regarding the other.

i.e. : sometimes i'll look 2022/2019, then 2021/2020...

octomore_0-1651501531745.png

I created a second table, with ony a year colums (my second filter)...but it doesn't work at all

 

Could you please help me a bit on this please?

tyvm and sorry for my poor english.

Octomore

 

1 ACCEPTED SOLUTION

If you want to compare random fields e.g.:-

- 2022 vs 2019

- 2022 vs 2021

- 2021 vs 2015 ...

 

then you cant do it will 2 slicers on the same dataset,

because when you slice by 2022 the data will only be for 2022.

 

The solution is to use 2 copies of the data each with a slicer.
With no relationship with each other.

 

Then have 3 measures:-

YearA = SUM(SalesfileA[value])

YearB = SUM(SalesfileB[value])

Difference = YearA - YearB

 

You can then have 2 slicer

SalesfileA[Year]  and SalesfileB[Year]

 

You can imporve the solution using 2 Calendar tables, but the principle is the same.

 

Please click the thumbs up and Accept As Solution

 

 

 

View solution in original post

7 REPLIES 7
speedramps
Super User
Super User

Thank you octomore 😀

 

 

speedramps
Super User
Super User

Do you want to always compare previous year

eg 2009 to 2010, 2010 to 2011, 2011 to 2012

or do you need to compare 2009 to 2012?

 

You cant use a 2 silcers on the same date.

If you slice sales for 2020 then the seconds slicer will only show 2020.

 

The video link I gave you explains how to best compare years using an offset.

 

If you want to be a Power BI developer then I recommed you do the Calendar training again.

Thanks again, 

I have to compare "offset" periods, but not allways the same ones

- 2022 vs 2019

- 2022 vs 2021

- 2021 vs 2015 ...

That's the reason why i have to use 2 parametres (current one and refference one) 

But unfortunately i'm no able to do it ... for the moment ! may be my mind is too Excel-focused to solve it.

🙂

If you want to compare random fields e.g.:-

- 2022 vs 2019

- 2022 vs 2021

- 2021 vs 2015 ...

 

then you cant do it will 2 slicers on the same dataset,

because when you slice by 2022 the data will only be for 2022.

 

The solution is to use 2 copies of the data each with a slicer.
With no relationship with each other.

 

Then have 3 measures:-

YearA = SUM(SalesfileA[value])

YearB = SUM(SalesfileB[value])

Difference = YearA - YearB

 

You can then have 2 slicer

SalesfileA[Year]  and SalesfileB[Year]

 

You can imporve the solution using 2 Calendar tables, but the principle is the same.

 

Please click the thumbs up and Accept As Solution

 

 

 

Thanks a lot for your help, you compleatly answer to my request.

Have a nice day/evening

octomore
New Member

Thanks a lot for your answer speedramps,

I allready watched a lot of tutorials, including this one, but i'm still unable to apply them to the case i presented. 

I'll continue to search on my side, but if you've an idea...don't hesitate !!!

speedramps
Super User
Super User

Hi octomore

 

If you are a Power BI Novice then learn how to use Calandar tables

Click here to watch Calendar video 

 

If you are new then please click the thumbs up and Accept As Solution to encourage us to help you again in future.
Thank you !

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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