DAX - Calculated Column based on On-prem VM sizing.

[WlConley]

Hi All, 

I am trying to create a calculated column based on a set of on-prem Virtual Machine attributes (CPU, RAM, Target Azure Region). 

The calculation should evaluate the existing attributes and best match them to an Azure VM Sku, based on its CPU, RAM, and Available region. (Getting this data from the Azure Retail Prices API for VM's)

I have been able to get the formula to populate an Azure VM Sku - But its not right and only providing one Sku for all on-prem VMs.

Then once a Sku has been assigned, there are 3 additional columns that will be populated, showing the Azure VM CPU and RAM sizes, along with the Retail prices for both Windows and Linux OS... However once its calculated, I am only getting back the Linux retail prices and not the Windows prices... Any help would be greatly appreciated.  Screenshots are down below, along with the DAX formulas and 

Sku Assigner

Azure VM Sku Calculated Column:

PAYG_TargetName =

VAR myMeasure_vCPU = [CPU]

VAR myMeasure_RAM = [RAM (GB)]

VAR TargetRegion = [Region]

VAR AzVMSeries =

    SELECTCOLUMNS ( 'AZVMProductNames', "productName", [productName] )

VAR AzTarget =

    MAXX (

        TOPN (

            1,

            CALCULATETABLE (

                'PAYG VM Pricing',

                ISNUMBER ( 'PAYG VM Pricing'[AzResourceVM.vCPUsAvailable] ),

                'PAYG VM Pricing'[Items.productName] IN AzVMSeries,

                'PAYG VM Pricing'[Items.location] = TargetRegion

            ),

            ABS (

                CALCULATE (

                    ( myMeasure_vCPU ) - MAX ( 'PAYG VM Pricing'[AzResourceVM.vCPUsAvailable] )

                )

            ), ASC,

            ABS (

                CALCULATE ( myMeasure_RAM - MAX ( 'PAYG VM Pricing'[AzResourceVM.MemoryGB] ) )

            ), ASC,

            'PAYG VM Pricing'[Items.unitPrice], ASC

        ),

        'PAYG VM Pricing'[Items.armSkuName]

    )

RETURN

    AzTarget

PAYG Retail Pricing Column:

PAYG Hour =

VAR ArmSkuName = [PAYG_TargetName]

VAR AzLocation = [Region]

VAR isWindows = [Windows?]

RETURN

MAXX(

   CALCULATETABLE('PAYG VM Pricing',

      'PAYG VM Pricing'[isWindows] = isWindows,

      'PAYG VM Pricing'[Items.armSkuName] = ArmSkuName,

      'PAYG VM Pricing'[Items.location] = AzLocation),

   CONVERT('PAYG VM Pricing'[Items.unitPrice], DOUBLE)

)

[Anonymous]

Hi @WlConley 

Can you provide some sample data as well as the expected output of the 4 columns in table format that can be copied? Can you explain the logic of the sku column that you want to achieve? 

Best Regards,
Jing

[WlConley]

Hi @Anonymous 

Yes, the API used to pull in the retail pricing for VM's is the following API link :  Azure Retail Price VM API 

Link to sample data is located here - Sample Data sets (Azure VM Sku's, VM Input List) 

The output I would like to achieve would look like the following:

VM Name	CPU	Memory (RAM)	Operating System	Target Azure Region	Azure PAYG Target Name	Azure CPU	Azure Memory (RAM)	PAYG Retail Hour Price
VM01	2	8	Windows	US East 2	Standard_D2_v3	2	8	$0.22
VM02	8	16	Linux	US East 2	Standard_F8s	8	16	$0.65

The logic I am trying to write does a few things:

1. Match the Target region with the Azure Retail price API region so that it only looks at Azure VM's in that region
2. Compare the onprem VM CPU's and Memory to the Azure Vm CPU's and Memory to pick which Azure Sku best fits (Like for Like comparison)
3. If the VM operating system is windows, bring back the Azure SKU (VM) Windows retail pricing, if the operating system is linux, then bring back the linux pricing.

If you are having trouble accessing the sample files, please let me know!