Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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 NameCPUMemory (RAM)Operating SystemTarget Azure RegionAzure PAYG Target NameAzure CPUAzure Memory (RAM)PAYG Retail Hour Price

      VM01

      		28WindowsUS East 2Standard_D2_v328$0.22
      VM02816LinuxUS East 2Standard_F8s816$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!