Stack Bar Using Measure from different table

[ashaikh]

Hello,

I have 3 tables that I am using viewProdRemedyIncidentServicesSla(TableA), viewProdRemedyWorkorders(TableB) and Date table.

TableA and TableB have a measure to calculate Resolved Tickets in a weeks. These table are join to Date table using DateKey column.

Date table has columns like Date, DateKey, WeekDuration, Month, etc.

Measures created for TableA and TableB is below -

TableA

INCIDENT COUNT RESOLVED =
CALCULATE (
    DISTINCTCOUNT ( viewProdRemedyIncidentServicesSla[INCIDENT NUMBER] ),
    FILTER (
        ALL ( viewProdRemedyIncidentServicesSla ),
        ( viewProdRemedyIncidentServicesSla[STATUS] = "Closed"
            || viewProdRemedyIncidentServicesSla[STATUS] = "Resolved" )
            && viewProdRemedyIncidentServicesSla[WeekNumberResolved]
                = MAX ( viewProdRemedyIncidentServicesSla[WeekNumberSubmit] )
            && viewProdRemedyIncidentServicesSla[INCIDENT LAST RESOLVED DATE].[Year] = 2018
    )
)

TableB

WORKORDER COUNT RESOLVED =
CALCULATE (
    COUNT ( viewProdRemedyWorkorders[WORK ORDER ID] ),
    FILTER (
        ALL ( viewProdRemedyWorkorders ),
        ( viewProdRemedyWorkorders[STATUS] = "Closed"
            || viewProdRemedyWorkorders[STATUS] = "Completed" )
            && viewProdRemedyWorkorders[WeekNumberResolved]
                = MAX ( viewProdRemedyIncidentServicesSla[WeekNumberSubmit] )
            && viewProdRemedyWorkorders[ACTUAL END DATE].[Year] = 2018
    )
)

Using these I want to create a stack bar - 

Thanks in Advance

[v-jiascu-msft]

Hi @ashaikh,

Are they two measures? If so, you can add them to the Value field of the Stacked Column chart. Please refer to snapshot below.

Best Regards,
Dale

[ashaikh]

Yes they are 2 measure. If you see the formula how they are build, because of that it does not properly get mapped based on week duration.

By this what do I mean is, the value shown for each date or each category is same.

[v-jiascu-msft]

Hi @ashaikh,

Can you share the file or a dummy sample? 

1. Do these tables have proper relationships?

2. The weeks should be from the Date table.

Best Regards,
Dale

[ashaikh]

@v-jiascu-msft yes they have proper relationships.

By the looks what is happening is if you look at the measure in filter I am using ALL for the table. And I have to use that because I am generating weekly report and I want to consider all the tickets which have been resolved in the particular irrespective of when they were created. 

[v-jiascu-msft]

Hi @ashaikh,

Can you share your file or a sample?

Best Regards,
Dale