% of Quarter on Bar Chart

[StoryofData]

Hi, I have a 'Calendar' and 'Query1' data table, I am connecting the two based on 'Calendar'[Date] to 'Query 1'[Date]

Sample Data: 

Attribute	Date	CaseNumber	CaseOutcome
Date Closed	1/30/2003	L1-00	Not Substantiated
Date Opened	10/3/2002	L1-00	Not Substantiated
Date Closed	10/22/2003	L2-00	Not Substantiated
Date Opened	10/1/2003	L2-00	Not Substantiated
Date Closed	1/3/2005	L3-00	Substantiated
Date Opened	1/1/2005	L3-00	Substantiated

I am trying to show % of substantiated cases by quarter on a bar chart. 

It was doing a percent of grand total initially so I tried to create a measure like this: 

=

CALCULATE(

DIVIDE(

DISTINCTCOUNT([CaseNumber]),

FILTER('Query1',[CaseOutcome] = "Substantiated"),

DISTINCTCOUNT([CaseNumber])),ALLEXCEPT ('Calendar','Calendar'[Fiscal Year]))

But I am getting an error: expression refers to multiple columns. Multiple columns cannot be converted to scalar value.
What am I doing wrong?

[v-stephen-msft · Accepted solution]

Hi @StoryofData ,

The error message you are seeing is because the DISTINCTCOUNT function is expecting a single column as input, but you are passing it multiple columns.

You can still use your measure, but with a few changes:

aa =
VAR _count1 =
    CALCULATE (
        DISTINCTCOUNT ( [CaseNumber] ),
        FILTER ( 'Query1', [CaseOutcome] = "Substantiated" )
    )
VAR _count2 =
    CALCULATE (
        DISTINCTCOUNT ( [CaseNumber] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Fiscal Year] )
    )
RETURN
    DIVIDE ( _count1, _count2 )

Or please try this measure, you can then change it based on your formula

Measure = var _count1=CALCULATE(DISTINCTCOUNT('Query 1'[CaseNumber]),FILTER('Query 1',[CaseOutcome]="Substantiated"))
var _count2=DISTINCTCOUNT('Query 1'[CaseOutcome])
return DIVIDE(_count1,_count2)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[StoryofData]

also tried this: