QS Table showing duplicate records when using if condition in expressions (measures)

[Anilb1603]

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

[Anilb1603]

Did anyone get a chance to look at this one??