Converting Report Services Queries to Power BI

[jmiridium]

I have a report that I am trying to convert to PowerBI. I would like to have cards that display the following information:

Service Requests Awaiting Triage:

select count(*) AS 'SR Needing Triage'
FROM ServiceReq
where (OwnerTeam ='Heat Admins' or substring(ownerTeam,1,3)='IT ')
and status NOT IN ('Closed', 'Fulfilled', 'Cancelled','Approved')
and (Select count(*) from Task where ParentLink_RecID=ServiceReq.RecId and (Task.Status not in ('Cancelled','Completed','Rejected')or Task.Status is NULL) )=0

Open Service Requests:

Select count(*)AS 'Open Service Requests' From ServiceReq
Where (OwnerTeam ='Heat Admins' or substring(ownerTeam,1,3)='IT ')
and status NOT IN ('Closed', 'Fulfilled', 'Cancelled','Approved')

HD Awaiting Triage:

Select count(*) as 'HD Needing Triage'
from Incident
where iri_customertype='Internal' and status NOT IN ('Closed', 'Resolved')
and isnull((Select count(*) from Task where task.ParentLink_RecID=Incident.RecId and (Task.Status in ('Cancelled','Completed','Rejected','Reassigned'))),0) =0

Open Help Desk:

Select count(*) AS 'Open Help Desk'from Incident
where iri_customertype='Internal' and status NOT IN ('Closed', 'Resolved')

Also add a table of the following query:

Select a.OwnerTeam As "Group Name"
    ,SUM(1) as "Open Assignment"
    ,SUM(CASE WHEN cl.IRI_CustomerType='Internal' THEN 1 ELSE 0 END) as "Help Desk"
    ,SUM(CASE WHEN cl.IRI_CustomerType='Internal'and datediff(day,cl.CreatedDateTime,getdate()) > 3 AND cl.Source<>'Internal' THEN 1 ELSE 0 END) as "HD >3 Days"
    ,SUM(CASE WHEN ParentLink_Category ='ServiceReq' THEN 1 ELSE 0 END) as "Service Request"
    ,SUM(CASE WHEN ParentLink_Category ='ServiceReq' and datediff(day,sr.CreatedDateTime,getdate()) > 7 THEN 1 ELSE 0 END) as "SR >7 Days"
        FROM Task a left join Incident cl on a.ParentLink_RecID=cl.RecID
        left JOIN ServiceReq sr ON a.ParentLink_RecID=sr.RecId
                    
        WHERE a.OwnerTeam IN ('Heat Admins','IT Backup admins','IT DBA Admins','IT Help Desk Admins','IT Management','IT Network Admins','IT Project Management','IT SAN Admins','IT Security Admins','IT Unix Admins','IT VMWare Admins','IT Windows Admins')
        AND a.Status not in ('Cancelled','Completed','Rejected')  
Group by a.OwnerTeam
Order By a.OwnerTeam

Can this be done?

[parry2k]

it is easy, you can use filters to include/exclude record and/or create DAX measures to do various aggregations, if need help, just provide sample data in excel and will put together something for you

[jmiridium]

I have cards for Open Service Requests and Open Help Desk done. That was easy. However it's this part in the other cards:

where iri_customertype='Internal' and status NOT IN ('Closed', 'Resolved')
and isnull((Select count(*) from Task where task.ParentLink_RecID=Incident.RecId and (Task.Status in ('Cancelled','Completed','Rejected','Reassigned'))),0) =0

[parry2k]

It can be achieved by DAX formula, basically you need to apply filter expression in DAX. If you can send a sample data in excel sheet, will able to create something, in the meantime, here is the link for you to take a look.

You need to use calculate function with filter expression

https://msdn.microsoft.com/en-us/library/ee634825.aspx?f=255&MSPPError=-2147217396