Hello,
I created this chart as below. The data model was built from two tables "Case" and "Report", linked with Case_ID as primary key. Case_ID and Report_ID are dimensions, the rest of data fields are expressions.
Since there are NULL values in the Case_ID, so I have the expression =If (not IsNull([Case Number]) AND not isnull(Report_ID),[Case Number]) for Case_ID.
There are duplicated values in "Report_ID" field. I wasn't sure why those duplicated values exsit as there is no duplicated value in the table "Report". I would like only show distinct values which not only the chart will look better and clean, as well as I will have an accurate values in "Submission Count".
| Case_ID | Report_ID | Submitted Date | Submission Count |
| 2003-0509 | 60303 | 5/9/2003 | 1 |
| - | 60303 | 5/9/2003 | 1 |
| 2004-0615 | 60506 | 6/15/2004 | 1 |
| 2005-0820 | 80504 | 8/20/2005 | 1 |
| - | 80504 | 8/20/2005 | 1 |
tried this in script (while loading data fields from Report table), but doesn't work.
LOAD
Distinct REG_REPORT_ID ,
REG_REPORT_ID as Report_ID.
I am not sure if try =count({$<[Submission Count] = {'1'}>} >} distinct Report_ID) would work for this case. Becuase I also want to eliminate those duplicated rows.
What will be your suggestion to help me solve this issue?
Thanks for your help in advance,
Becky
