cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nikahafiz Regular Visitor
Regular Visitor

Slice by Year in Table Visualization

Hi, 

To begin with, I have created 3 measures to calculate Total Fees and do a month-on-month comparison as follows:

1.  TTL Fees = Calculate(sum('Table1'[Amount]), 'Table1'[AccountLevel3]="Fees - Income")

 

2. TTL Fees LM = CALCULATE([TTL Fees],DATEADD('Table1'[Month-Year],-1,MONTH))

 

3. TTL Fees Inc Var = if([TTL Fees LM] = BLANK(), "N/A", [TTL Fees]-[TTL Fees LM])

 

I'm able to generate a table visualization as attached

 

Screen Shot 2019-10-07 at 15.14.56.pngtable visualization with the 3 measures

However, I am not able to use a slicer based on my calendar table for this table visualization as it'll result in blanks for the 'TTL Fees LM' measure since it uses 'Table1'[Month-Year]. Kindly advise on how to resolve this.

 

Tks: -Nik

 

4 ACCEPTED SOLUTIONS

Accepted Solutions
rfarago Visitor
Visitor

Re: Slice by Year in Table Visualization

Hi @nikahafiz !

You are most likely missing the relation between the Date column of your Table1 and the CalendarTable (in my case DateDIM). And make sure you use the CalendarTable value in your slicer.

 
 
relation.pngyearm.png
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Kind Regards,
Roland



 

 

 

View solution in original post

nikahafiz Regular Visitor
Regular Visitor

Re: Slice by Year in Table Visualization

Thanks for your swift reply, Roland.

 

I have actually gotten the results as you have gotten too. However, when it comes to slicing to a specific period, I only want to table visual to only display info for the selected period i.e. year(s) in this case.

In my new visual below, I have selected '2017' but the table visual also/still shows info for '2018' which I don't wish to have:

 

Screen Shot 2019-10-07 at 16.50.55.png

I need the table visual to only show info for '2017'.

Thanks: -Nik

View solution in original post

Community Support Team
Community Support Team

Re: Slice by Year in Table Visualization

Hi @nikahafiz ,

 

The issue should be related your measures in the table visual. We can use blank() instead of "N/A". If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

nikahafiz Regular Visitor
Regular Visitor

Re: Slice by Year in Table Visualization

Thanks for your message too, Frank.

 

Yes, after opting to use blank() instead of "N/A", I have managed to solve the problem & now able to do use a year-to-select slicer too.

Kind regards, -Nik  

View solution in original post

4 REPLIES 4
rfarago Visitor
Visitor

Re: Slice by Year in Table Visualization

Hi @nikahafiz !

You are most likely missing the relation between the Date column of your Table1 and the CalendarTable (in my case DateDIM). And make sure you use the CalendarTable value in your slicer.

 
 
relation.pngyearm.png
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Kind Regards,
Roland



 

 

 

View solution in original post

nikahafiz Regular Visitor
Regular Visitor

Re: Slice by Year in Table Visualization

Thanks for your swift reply, Roland.

 

I have actually gotten the results as you have gotten too. However, when it comes to slicing to a specific period, I only want to table visual to only display info for the selected period i.e. year(s) in this case.

In my new visual below, I have selected '2017' but the table visual also/still shows info for '2018' which I don't wish to have:

 

Screen Shot 2019-10-07 at 16.50.55.png

I need the table visual to only show info for '2017'.

Thanks: -Nik

View solution in original post

Community Support Team
Community Support Team

Re: Slice by Year in Table Visualization

Hi @nikahafiz ,

 

The issue should be related your measures in the table visual. We can use blank() instead of "N/A". If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

nikahafiz Regular Visitor
Regular Visitor

Re: Slice by Year in Table Visualization

Thanks for your message too, Frank.

 

Yes, after opting to use blank() instead of "N/A", I have managed to solve the problem & now able to do use a year-to-select slicer too.

Kind regards, -Nik  

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors