Skip to main content
cancel
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

Reply
dexter00000
Helper I
Helper I

Slicing a table by Month and Year From Same Date Table

Hi,

 

I have a date table and another table. Both have YEAR,MONTH columns. But because there is only 1 active connection i can only slice YEAR or MONTH.I don't want to multiply the date table to use 2 different slicers. I want to be able to slice them separately with 2 slicers by only 1 date table. How can i achieve this?

 

Thanks, 

1 ACCEPTED SOLUTION

14 REPLIES 14
dexter00000
Helper I
Helper I

so this is how it looks with additional columns. 

I only have 1 dates table and no other dimension tables year will slice the fact table and month will too they can be used together. But also separately too. Is this achievable?

 

sss.jpg

You don't have a Dates table yet. The Date column is missing. For Time Intelligence functions to work that date column must be contiguous (no gaps) and covering (including the result of the time intelligence).

 

Please show a sanitized version of your data model.

I didn't include them in the picture but it's like this.

 

dates.jpg

data.jpg

AY,DÖNEM are the same thing. Messing around got those a bit confusing. Basically AY,DÖNEM(MONTH) and YIL(YEAR) will be sliced by the date table. But i don't know how will i achieve it with only 1 connection.

 

data2.jpg

Your Query2 is not marked as a Dates table. 

In your Query1 add a surrogate column with the first day of the year and month of your fact data.  Link both tables via that column Query2 1:* Query1, single direction.

 


@lbendlin wrote:

first day of the year and month of your fact data.  Link both tables via that column Query2 1:* Query1, single direction.

 


The way you described it i couldn't understand. First day of the year 1.01.2022 alright, and month? another column or not another column, February or  1.02.2022? I'm not sure could you describe it a bit more clearly.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Hi again,

 

Okay i made a pbix file for it in onedrive and again got some screenshots for what's desired.

 

So basically month slicer can slice when it's Date Table.Month Name:*-Fact Table.Month:*

 

But can not slice by year.

 

Again with Date Table.Year:*-Fact Table.Year:* can slice the fact table by year but not by month.

 

monthslicer.jpg

yearslicer.jpg

 

What's desired is that i need to make a connection that can separately slice by both but at the same time it should work together too.

 

By what i've understand i'm supposed to use the time intelligence Date column but i can't do it by my Fact Table.Year or Fact Table.Month. So how can i achieve this?

 

yearandmonthslicer.jpg

Here is the onedrive link: datetableexample.pbix

see attached

lbendlin_1-1707862541940.png

 

Okay, this is what i was trying to achieve thank you so much!

 

Now i understand what you meant by adding first date of each month.

I'm gonna look into time intelligence more from now on. Used to do month and year slices by the year and month columns only.

 

Thank you so much again,

Have a good day.

I'm sorry if i'm a bit slow on this. Do you mean a single column with 1.01.2022,1.02.2022,1.03,2022,...,1.01.2023,1.02.2023,...,1.01.2024,1.02.2024 values? And after i do this column i'll link it to time intelligence(Date) column? I could use a visual or an example on this.

lbendlin
Super User
Super User

Add a derived date to your fact table (for example first day of the month) and link that to your date table.

Hi i'm not sure how will that work. I tried first day of the month to first day of the month way,i tried Date Hierarchy to first day of the month. But none of them work. Am i supposed to have only 1 connection? YEAR and MONTH will be 2 different slicers that are in the same table.

If you want these slicers to work independently then you need separate year and month dimension tables that control the Dates table. If they should interact, use the year and month columns from the Dates table. Do not base the slicer on the fact table .

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors