Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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;
When i simply create the pivot table i get the output like this;
What i want to see is;
Is this achievable on PowerBI?
Any help will be appreciated. Thanks a lot.
Solved! Go to Solution.
@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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Hi @parry2k
Thanks for your quick answer but the real challenge that i'm facing is to not see totalPbAmount column under other dates.
@mfarsln unfortunately that is not possible, you can hide the value thru measure but you cannot complete get rid of the column
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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 :
Y | X | Current Status | Last Month | Last Month AMT | Follow Up | Follow Up Amount | Contract | Contract Amount | Mark |
Non OD | Last Month | Close | 27064121 | 240449794 | |||||
Non OD | Follow Up | Close | 235254872 | 18002680 | 0 | 0 | |||
Non OD | Current | Close | 35454710 | 169387645 | Today | ||||
Non OD | 1-10 days | Close | 248571105 | 146503249 | Today | ||||
Non OD | 11-30 days | Close | 191092798 | 101839458 | Today | ||||
Non OD | 31-60 days | Close | 92167890 | 162348094 | Today | ||||
Non OD | 61-90 days | Close | 146658984 | 188426619 | Today | ||||
Non OD | >= 91 days | Close | 183850816 | 83246834 | Today | ||||
1-10 days | Last Month | Close | 221312687 | 31892321 | |||||
1-10 days | Follow Up | Close | 170272277 | 147666239 | 0 | 0 | |||
1-10 days | Current | Close | 191270430 | 176167164 | Today | ||||
1-10 days | 1-10 days | Close | 53310230 | 220455694 | Today | ||||
1-10 days | 11-30 days | Close | 193824565 | 167280973 | Today | ||||
1-10 days | 31-60 days | Close | 205658896 | 59092391 | Today | ||||
1-10 days | 61-90 days | Close | 3687627 | 42430887 | Today | ||||
1-10 days | >= 91 days | Close | 99077002 | 100167777 | Today | ||||
11-30 days | Last Month | Close | 155473711 | 9826176 |
when i simply pivot appear like this image in url:
i want to show only the cell with value, could you help me to solve this
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.
@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'))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Thank you so much for your time. It works like magic when i change it to ALLSELECTED.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |