Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

[vvira1316]

Hi,

I've a straight table with following two dimensions Original API and Table Name

I've attached sample data set with simple data model, but it is not same in my actual scenario. I've included a screen shot of it for understanding. It will be difficult for me to provide that here. If required I can try to replicate it.

Screen shots of Actual Table and KPI Calculations

Portion of Data Model for above can be seen in screen shot below

Thanks,

Vijay

[sunny_talwar · Accepted solution]

Well one is a distinct count the other one is not.... May be you need Sum(Aggr()) here

=Sum({<[Table Name]>}Aggr(Count(DISTINCT {<[Table Name]>} [Original API Name]), [Original API Name], [Table Name]))

or DISTINCT Count [Original API Name]&[Table Name]

=Count(DISTINCT {<[Table Name]>} [Original API Name]&[Table Name])

[rwunderlich]

Why aggr()?  Just removing the DISTINCT makes both the table and the KPI return the same value.

-Rob

[sunny_talwar]

I was not sure why use DISTINCT in the first place... may be there is a reason to use it... but yes, you are right... removing DISTINCT should do it in this case

[vvira1316]

Hi Sunny,

Yes, both expressions are working. Thanks.

[vvira1316]

Removing distinct provides higher count.

=Count({<[Table Name]>} [Original API Name]&[Table Name])

Count is 3544

it may be due to values of other dimensions within those two tables from the actual data model.

[vvira1316]

Both these expressions provided expected count of 1103.

[sunny_talwar]

Not removing DISTINCT from this

=Count({<[Table Name]>} [Original API Name]&[Table Name])

This should use DISTINCT

=Count(DISTINCT {<[Table Name]>} [Original API Name]&[Table Name])

Rob mentioned about removing DISTINCT from this

=Count(DISTINCT {<[Table Name]>} [Original API Name])

But I am not sure what you are really doing and that is why I didn't suggest this to you in the first place

[vvira1316]

Yes, I had tried that before posting the question. It gives me count of 3544. In this dashboard user is trying to use transactional information with metadata of tables where that information is getting stored. In order to do all that I had to juggle data model and data processing to achieve the functionality the user wanted. I've been able to achieve all of that so far. This count was off which I had noticed so I wanted to take care of it and I was having challenge to get it working.

Thanks to you and Rob for helping us out.

Best Regards,

Vijay