Lookup value with 2 criteria from 2 tables

[Anonymous]

Hi All,

I have a situation where I am trying to look up the price of a VM based on its profile, and its region (from a central rate card)

This is the DAX code I am currently using:

As-Sized PAYGO = 
//Restirct to Just Windows Devices
IF (
    [Is Linux] = FALSE (),
    IF (
        'Device_Azure_Costs'[As-Size Override Active] = True (),
        //If True (Override in place)
        LOOKUPVALUE (
            AzurePricingList[PayGoWin],
            AzurePricingList[Profile], 'Device_Azure_Costs'[As-Size VM Override Profile],AzurePricingList[MeterRegion],Device_Azure_Costs[CustomerDC]
        ),
        //else (if No Override in place)
        LOOKUPVALUE (
            AzurePricingList[PayGoWin],
            AzurePricingList[Profile], 'Device_Azure_Costs'[As-Size VM Profile],AzurePricingList[MeterRegion],Device_Azure_Costs[CustomerDC]
        )
    ),
    //Do Nothing
    0
)
//Convert to Rate
*[Rate]

Currently, the DAX returns blank values.

Ideally, I would like the solution to:

Check if the current Profile has an override in place
     if it does, look up the overriding profile, in the rate card, taking into account the DC that is specified for the machine in question

   if there is no override, look up the provided profile in the rate card, taking into account the DC that is specified for the machine in question.

From that point I know I can wrap that in check statements for Linux and SQL where required.

How would I go about doing this?  I've included sample data below if that helps.

Sample Data:

Destination table: [Device_Azure_Costs]

Device Name

Azure Region

Is Linux

As-Size Override Active

Right-Size Override Active

As-Sized PAYGO

Right-Size VM Override Profile

As-Size VM Override Profile

Right-Size VM Profile

As-Size VM Profile

Is SQL Server

Is SQL Server STD

Is SQL Server ENT

Is SQL Server NonCost

Customer DataCenter

WS2016DC03.Contoso.com

westus

False

False

False

[value I want to Compute]

[possible override profile]

D1_v2

D1_v2

False

False

False

False

UK South

BIZTALKSQL.Contoso.com

westus

False

False

False

[value I want to Compute]

[possible override profile]

D1_v2

DS15_v2

True

True

False

False

West US

[Lookup Table: AzurePricingList]

Profile	MeterRegion	PayGoWin
A2	UK South	144.54
A1_v2	UK South	55.48
A1	EU West	72.27
A0	UK South	16.06
A2_v2	UK South	116.07
A2m_v2	West US	197.1
A3	UK South	289.08
A4_v2	UK South	243.09

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Based on your sample, I add 2 rows into AzurePricingList,

then I change your dax expression as bellow

columnTest = 
//Restirct to Just Windows Devices
IF (
    [Is Linux] = FALSE (),
    IF (
        'Device_Azure_Costs'[As-Size Override Active] = True (),
        //If True (Override in place)
        LOOKUPVALUE (
            AzurePricingList[PayGoWin],
            AzurePricingList[Profile], 'Device_Azure_Costs'[As-Size VM Override Profile],AzurePricingList[MeterRegion],Device_Azure_Costs[Customer DataCenter]
        ),
        //else (if No Override in place)
        /*LOOKUPVALUE (
            AzurePricingList[PayGoWin],
            AzurePricingList[Profile], 'Device_Azure_Costs'[As-Size VM Profile],AzurePricingList[MeterRegion],Device_Azure_Costs[Customer DataCenter]
        )*/
        CALCULATE(MIN(AzurePricingList[PayGoWin]),FILTER(ALL(AzurePricingList),AzurePricingList[Profile]==Device_Azure_Costs[As-Size VM Profile]&&AzurePricingList[MeterRegion]==Device_Azure_Costs[Customer DataCenter]))
    ),
    //Do Nothing
    0
)
//Convert to Rate
//*[Rate]

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much, this solved my problem!

[Anonymous]

Hi Jos,

The Linux statement is not the part that is failing i dont think.

The clause below is failing I believe:

      LOOKUPVALUE (
            AzurePricingList[PayGoWin],
            AzurePricingList[Profile], 'Device_Azure_Costs'[As-Size VM Profile],AzurePricingList[MeterRegion],Device_Azure_Costs[CustomerDC]

What  I would expect to happen is for the Given profile [As-Sized VM Profile] Taken from Device Azure Costs, to be looked up in PricingList, while also taking into account the DC referenced in [Customer DC]

Larger sample of Pricing List: 

Profile	MeterRegion	PayGoWin
A1_v2	AU Central	55.48
A2	KR South	144.54
A1	UK South 2	86.87
A0	FR South	20.878
A2_v2	JA West	124.1
A2m_v2	US Gov TX	175.2
A3	KR South	289.08
A4_v2	UK West	243.09
A4m_v2	AE Central	370.84
A5	EU North	219
A6	IN Central	445.3
B1ls	SE South	8.03
B1ms	CH West	30.222
B1s	US North Central	10.512
B2ms	KR Central	87.6
B2s	EU North	39.128
B4ms	US East 2	132.86
D1	ZA North	99.28
D1_v2	US West	91.98
D11	BR South	294.19
D11_v2	FR South	292
D12	EU West	408.07
D12_v2	ZA North	427.05
D2	AU Southeast	227.76
D2_v2	EU North	169.36
D2_v3	AE North	154.76
D2_v4	AU Southeast	158.41
D2a_v4	EU West	151.11
D2d_v4	US Gov AZ	171.55
D2ds_v4	ZA West	211.7
D2s_v4	IN West	153.3
D3	US South Central	379.6

[Larger Sample of Device_Details]

Device Name	Azure Region	Is Linux	As-Size Override Active	Right-Size Override Active	As-Sized PAYGO	Right-Size VM Override Profile	As-Size VM Override Profile	Right-Size VM Profile	As-Size VM Profile	Customer DataCenter
WS2016DC03.Contoso.com	westus	False	False	False				D1_v2	D1_v2	UK South
BIZTALKSQL.Contoso.com	westus	False	False	False				D1_v2	DS15_v2	West US
SPSTD.Contoso.com	westus	False	False	False				D2a_v4	F4s_v2	UK South
WS2012R2STD23.Contoso.com	westus	False	False	False				D1_v2	F2s_v2	UK South
WS2008STD08.Contoso.com	westus	False	False	False				D1_v2	D1_v2	UK South
WS2016STD20.Contoso.com	westus	False	False	False				D1_v2	F2s_v2	UK South
WS2016STD08.Contoso.com	westus	False	False	False				D1_v2	D1_v2	UK South
WS2008R2WEB09.Contoso.com	westus	False	False	False				D1_v2	F2s_v2	UK South
REDHAT65.Contoso.com	westus	True	False	False				D1_v2	F2s_v2	UK South
WS2008R2ENT10.Contoso.com	westus	False	False	False				D1_v2	F2s_v2	UK South
UBUNTU1204.Contoso.com	westus	True	False	False				D1_v2	D1_v2	UK South
SQLENT2000.Contoso.com	westus	False	False	False				D1_v2	F2s_v2	UK South
WS2016DC01.Contoso.com	westus	False	False	False				D1_v2	D1_v2	UK South
WS2008R2STD07.Contoso.com	westus	False	False	False				D1_v2	F2s_v2	UK South
WS2012R2STD12.Contoso.com	westus	False	False	False				D1_v2	F2s_v2	UK South

Right now, it doesn't appear to be taking into account the second criteria (the region)

[Jos_Woolley]

I'm still not sure what you mean. For the new dataset you've provided, I would still expect As-Sized PAYGO to return all blanks, since there are no rows which match the criteria you specify within the LOOKUPVALUE construction.

In fact, your new version of the Device_Azure_Costs table comprises only 3 distinct combinations of Right-Size VM Profile and CustomerDC, i.e.:

D1_v2 with UK South

D1_v2 with West US

D2a_v4 with UK South

yet none of these 3 combinations can be found in the AzurePricingList table, so naturally LOOKUPVALUE returns BLANK.

Regards

[Jos_Woolley]

Hi,

Could you provide more realistic datasets? For the samples you've provided, the two rows in the Device_Azure_Costs table have an Is Linux entry of FALSE, which means that your DAX statement is evaluating the second clause in both cases. However, in both of those cases there is no corresponding entry in the AzurePricingList table which matches the conditions specified, so naturallly they both return BLANK().

Regards