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

Show or Hide Pivot Table Column Conditionally

Hello,

 

I have a pivot table with one row dimension, one column dimension (date) and two measures. What i want to do is that showing two measures if only its last date in the context.

 

For the sake of clarity, let me put a basic sample of my need.

 

Here is my base table;

 

snip-1.PNG

 

 

 

 

 

 

 

When i simply create the pivot table i get the output like this;

 

snip-2.PNG

 

What i want to see is;

 

snip-3.PNG

 

Is this achievable on PowerBI?

Any help will be appreciated. Thanks a lot.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Show or Hide Pivot Table Column Conditionally

Hi @mfarsln 

MeasureSwitchAmount.png

 

You may download my PBIX file from here.
Hope this helps.

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
twitter - LinkedIn - YouTube - website

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Show or Hide Pivot Table Column Conditionally

@mfarsln you can surely achieve this by measure returning blank value if date is not the last date but that totalpbamount measure will still be under each date with no value and only show values on the last date, would that work?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Regular Visitor

Re: Show or Hide Pivot Table Column Conditionally

Hi @parry2k 
Thanks for your quick answer but the real challenge that i'm facing is to not see totalPbAmount column under other dates. 

Highlighted
Super User IV
Super User IV

Re: Show or Hide Pivot Table Column Conditionally

@mfarsln unfortunately that is not possible, you can hide the value thru measure but you cannot complete get rid of the column






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User III
Super User III

Re: Show or Hide Pivot Table Column Conditionally

Hi @mfarsln 

MeasureSwitchAmount.png

 

You may download my PBIX file from here.
Hope this helps.

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
twitter - LinkedIn - YouTube - website

View solution in original post

Highlighted
Regular Visitor

Re: Show or Hide Pivot Table Column Conditionally

Thank you so much @mwegener it worked.

 

But one last thing. If i don't filter date field it works perfectly. But if i filter date, say i select 01.01.2019 and 01.02.2019, it still recognizes 01.03.2019 as last date. So it shows pbamount for both dates. pbamount should only be visible under the last date of the given context.

 

And what is the logic behind these 1 and 2? I couldn't get what those numbers represent here.

snip-4.PNG

Highlighted
Super User IV
Super User IV

Re: Show or Hide Pivot Table Column Conditionally

@mfarsln I looked at @mwegener  solution and it is very clever, you need to change this to get the lastdate based on selection, change ALL to ALLSELECTED

 

var MaxDate = CALCULATE(MAX('Table'[date]),ALLSELECTED('Table'))

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Regular Visitor

Re: Show or Hide Pivot Table Column Conditionally

Thank you so much for your time. It works like magic when i change it to ALLSELECTED. 

Highlighted
New Member

Re: Show or Hide Pivot Table Column Conditionally

i have a same problem with this,

for my case is not based on table, i try to make the data blank, its still appear, 

my table like this :

YXCurrent StatusLast MonthLast Month AMTFollow UpFollow Up AmountContractContract AmountMark
Non ODLast MonthClose27064121240449794     
Non ODFollow UpClose  2352548721800268000 
Non ODCurrentClose    35454710169387645Today
Non OD1-10 daysClose    248571105146503249Today
Non OD11-30 daysClose    191092798101839458Today
Non OD31-60 daysClose    92167890162348094Today
Non OD61-90 daysClose    146658984188426619Today
Non OD>= 91 daysClose    18385081683246834Today
1-10 daysLast MonthClose22131268731892321     
1-10 daysFollow UpClose  17027227714766623900 
1-10 daysCurrentClose    191270430176167164Today
1-10 days1-10 daysClose    53310230220455694Today
1-10 days11-30 daysClose    193824565167280973Today
1-10 days31-60 daysClose    20565889659092391Today
1-10 days61-90 daysClose    368762742430887Today
1-10 days>= 91 daysClose    99077002100167777Today
11-30 daysLast MonthClose1554737119826176     

 

when i simply pivot appear like this image in url:

Pivot  

 

i want to show only the cell with value, could you help me to solve this

 
 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors