Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Merge Office Counts rows into single count

Hello - 

 

I am trying to combine multiple version counts of office into a single row. 

 

 

Basically i want the Piechart object to only show a count of Office 365 installations but the query is pulling back the language extension on the end of the display name. 

 

Is there a way to merge rows that start with "Microsoft Office 365%" or modify the query to count matches?

 

Here is my query:

 

SELECT DISTINCT
SYS.Name0
,ARP.DisplayName0 As 'Software Name'
,ARP.Version0 As 'Version'
FROM
dbo.v_R_System As SYS
INNER JOIN dbo.v_FullCollectionMembership FCM On FCM.ResourceID = SYS.ResourceID
INNER JOIN dbo.v_Add_REMOVE_PROGRAMS As ARP On SYS.ResourceID = ARP.ResourceID
WHERE
(ARP.DisplayName0 LIKE 'Microsoft Office 365%'
OR ARP.DisplayName0 LIKE 'Microsoft Office Professional%'
OR ARP.DisplayName0 LIKE 'Microsoft Office Standard %')
ORDER BY Name0 ASC

 

Thanks

  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous,


    Please use DAX below.

    New name = IF(LEFT(Query2[Software Name],20)="Microsoft Office 365","Microsoft Office 365",IF(LEFT(Query2[Software Name],39)="Microsoft Office Professional Plus 2013","Microsoft Office 2013",Query2[Software Name]))


    Regards,
    Lydia

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Anonymous,

    You can create a new column in your table below, then involve the column in your visual or calculation.

    New name = IF(LEFT(Table1[Display name],20)="Microsoft Office 365","Microsoft Office 365 installation",Table1[Display name])



    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you!! That worked - 

       

      Is there a way to continue the string to handle multiple variables? 

       

      ex. 

       

      New name = IF(LEFT(Query2[Software Name],20)="Microsoft Office 365","Microsoft Office 365",Query2[Software Name])

      and

      (LEFT(Query2[Software Name],39)="Microsoft Office Professional Plus 2013","Microsoft Office 2013",Query2[Software Name])

      • Anonymous's avatar
        Anonymous
        Not applicable

        Anonymous,


        Please use DAX below.

        New name = IF(LEFT(Query2[Software Name],20)="Microsoft Office 365","Microsoft Office 365",IF(LEFT(Query2[Software Name],39)="Microsoft Office Professional Plus 2013","Microsoft Office 2013",Query2[Software Name]))


        Regards,
        Lydia