Forum Discussion

ashaikh's avatar
ashaikh
Helper III
7 years ago

Stack Bar Based on Multiple Table Measure

Hello,

 

I have 3 tables;  viewProdRemedyIncidentServicesSla(TableA), viewProdRemedyWorkorders(TableB) amd Date Table.

 

TableA has measure to calculate resolved tickets. Calculation is as below - 

 

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
 )
)

Even Table B has a measure to calculate Resolved Ticket

 

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
 )
)

 

Where as Date table has columns like Date, DateKey, WeeklyDuration. TableA and TableB have relationship with Date table using DateKey column.

 

So using this how will I be able to create a stack bar visual. For eg. Stack Bar should look like below.

 

Any help is appreciated

 

Thanks in Advance 

 

 

 

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Hi ashaikh,

     

    Does the table structure in 'viewProdRemedyIncidentServicesSla' and 'viewProdRemedyWorkorders' same with each other? If they are, you could add the datekey column to both tables, add a column to TableA which has value 'TableA' as legend column, add a column to TableB which has value 'TableB' as legend column. Then append the two tables, after applied&closed, you can drag column Date[Datekey] as X-axis column, drag legend column as legend. 

     

    Regards,

    Jimmy Tao

    • ashaikh's avatar
      ashaikh
      Helper III
      7 years ago

      v-yuta-msft the table dont have same structure. But as I have mentioned all three tables are related to each other using DateKey column.

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        Hi ashaikh，

         

        Could you share some sample data if possible?

         

        Regards,

        Jimmy Tao