Forum Discussion
Help convert a table into a chart
Hi all,
I have simplied my question and upload an example file here:
https://drive.google.com/file/d/1sLSgz5iNBRoj07_yMIkgzpuXBxwfPVCk/view?usp=sharing
The raw data looks like this.
Case Table
Date Table
What I am trying to do here is to find out at the end of each month, what is the latest status the case is at. And I have successfully create a measure and show the result in a table.
But when I tried to convert the table into a line chart, things become funny. Why does the line Count of Disqulifed Case still there after 31/10/2019? What I am expecting to see if only disqualifed line between 30/06/2019 to 30/09/2019 and only converted line after 31/10/2019 as per the table.
Hi @Penn
It is ok?
Creating Measures
fill status = CALCULATE(FIRSTNONBLANK('Case'[EOM Status],1),FILTER(ALLSELECTED('Case'),'Case'[EOM Date]<=MAX('Case'[EOM Date]))) Disqulifed = CALCULATE(DISTINCTCOUNT('Case'[Case ID]),FILTER(ALLSELECTED('Case'[Case ID]),'Case'[fill status]="Disqualified")) converted = CALCULATE(DISTINCTCOUNT('Case'[Case ID]),FILTER(ALLSELECTED('Case'[Case ID]),'Case'[fill status]="Converted"))the 'date2' is a table created by me and has a relationship with the main table
date2 = CALENDARAUTO()Best regards
Maggie
Community Support Team _ Maggie Li
If this post helps, please consider Accepting it as the solution to help other members find it more quickly.
3 Replies
all measures can be found in the attached file
Hi @Penn
It is ok?
Creating Measures
fill status = CALCULATE(FIRSTNONBLANK('Case'[EOM Status],1),FILTER(ALLSELECTED('Case'),'Case'[EOM Date]<=MAX('Case'[EOM Date]))) Disqulifed = CALCULATE(DISTINCTCOUNT('Case'[Case ID]),FILTER(ALLSELECTED('Case'[Case ID]),'Case'[fill status]="Disqualified")) converted = CALCULATE(DISTINCTCOUNT('Case'[Case ID]),FILTER(ALLSELECTED('Case'[Case ID]),'Case'[fill status]="Converted"))the 'date2' is a table created by me and has a relationship with the main table
date2 = CALENDARAUTO()Best regards
Maggie
Community Support Team _ Maggie Li
If this post helps, please consider Accepting it as the solution to help other members find it more quickly.Thanks Maggie, works fine with the example, will integrate into my report.
