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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

table visualization with the 3 measurestable 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
Anonymous
Not applicable

Hi @Anonymous !

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

Anonymous
Not applicable

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

Hi @Anonymous ,

 

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

Anonymous
Not applicable

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
Anonymous
Not applicable

Hi @Anonymous !

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



 

 

 

Anonymous
Not applicable

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

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

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  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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