How to create a Power BI New Measure

I was recently working on a new Secure Boot State Power BI report and I wanted to use the native view from the ConfigMgr database that was created for the Secure Boot State hardware inventory extension (see Inventory Secure Boot State and UEFI with ConfigMgr). The value that gets returned is 0, 1 or null depending on how the hardware is configured. 0 means that Secure Boot is Off, 1 means it is On and null means that it is not reported and the system is probably running BIOS.

Those labels look much better than 0, 1 or nothing, so I wanted an easy way to modify the Power BI report without creating a custom query (which is also possible). Fortunately, Power BI has the ability to add a New Measure. For my New Measure, I selected New Column (New Measure is the other option). I gave my New Column the name “Secure Boot State”. The next thing was to translate the UEFISecureBootEnabled0 field into “On”, “Off”, or “BIOS”. Using the IF function, this was possible:

Secure Boot State = IF (ISBLANK(v_GS_UEFI_SecureBootState0[UEFISecureBootEnabled0]),”BIOS”, (IF (v_GS_UEFI_SecureBootState0[UEFISecureBootEnabled0]=1,”On”, (IF (v_GS_UEFI_SecureBootState0[UEFISecureBootEnabled0]=0,”Off”)))))

Now you can see that I have a new column and my source data set remains un-touched:

01 Power BI New Measure

This will be useful in creating my Power BI report for Secure Boot State.

Originally posted on http://miketerrill.net

3 thoughts on “How to create a Power BI New Measure

  1. How did you resolve the SQL query?

    join v_GS_UEFI_SecureBootState0 XX on S.ResourceID = XX. ResourceID

    What is the value of the XX?

    I’ve tried to look from the database views and other places and of course I’ve tried to google it, but cannot find the right values for that and cannot get the query working.

    • I had an comma mistake on my SQL query. This is my modified query and It’s working now.

      SELECT
      S.ResourceID,
      S.Name0 as Name,
      S.Client_Version0 as Version,
      OS.Caption0 as [Operating System],
      CS.Manufacturer0 as Manufacturer,
      CS.Model0 as Model,
      PB.SMBIOSBIOSVersion0 as BIOS,
      PR.Name0 as CPU,
      SUM(PM.Capacity0) as Memory,
      SUM(LD.Size0) as [Disc Size],
      SUM(LD.FreeSpace0) as [Disc Free],
      SB.UEFISecureBootEnabled0 as UEFI
      FROM
      v_R_System S
      join v_GS_OPERATING_SYSTEM OS on S.ResourceID = OS.ResourceID
      join v_GS_PHYSICAL_MEMORY PM on S.ResourceID = PM.ResourceID
      join v_GS_COMPUTER_SYSTEM CS on S.ResourceID = CS.ResourceID
      join v_GS_LOGICAL_DISK LD on S.ResourceID = LD.ResourceID
      join v_GS_PC_BIOS PB on S.ResourceID = PB.ResourceID
      join v_GS_PROCESSOR PR on S.ResourceID = PR.ResourceID
      join v_GS_UEFI_SecureBootState0 SB on S.ResourceID = SB.ResourceID

      WHERE LD.DeviceID0 = ‘C:’
      GROUP
      BY S.ResourceID, S.Name0, S.Client_Version0, OS.Caption0, CS.Manufacturer0, CS.Model0, PB.SMBIOSBIOSVersion0, PR.Name0, SB.UEFISecureBootEnabled0

      • Hi Teppo – glad you got it sorted and thanks for sharing. I typically don’t do any SQL in my Power BI reports – that is the beauty of it. If it can determine the relationship between the data sets, then it works out all of the joins for you.
        -Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s