Hi Guys,
I have the sample data like below:
| Project | Date | Type | Status | Budget | Actuals |
| P1 | 1/20/2020 | History | Submitted | 100,000 | 98,000 |
| P1 | 1/30/2020 | History | In Progress | 100,000 | 98,000 |
| P1 | 2/21/2020 | Current | Completed | 100,000 | 98,000 |
| P2 | 1/20/2020 | History | Submitted | 100,000 | 98,000 |
| P2 | 1/30/2020 | History | In Progress | 100,000 | 98,000 |
| P2 | 2/21/2020 | Current | Completed | 100,000 | 98,000 |
for this I created a adhoc report with 3 dimensions (Fieldname: Pick_Dims) and 2 measures (Fieldname: Pick Metrics).
When I select either Project or Status, I want to show the current status only for each project and when I select the Date, I want to show the full history of a project (show the project status by date).
So I created the expression like below:
If(SubStringCount(Concat(Pick_Dims, '|'), 'Date')=1,
Sum(Budget),
Sum({<Type={'Current'}>} Budget)
)
But here, when I select Project and Status, I am seeing the duplicate records. I don't want to use suppress 0 values option as I might have some valid 0 budget records in the data model. If I use just Sum({<Type={'Current'}>} Budget), It showing only one record. I assume that If condition causing the duplication.
Can someone please help me here.
Thanks,
Anil
