Replicate a Calculation from Excel in PowerBI

[vacuesta]

Hi. I am looking for user advice to replicate a vlookup type calculation that is happening in excel to POWER BI :   =IF(LOOKUPVALUE(Circuit_Speeds[BW/Speed Validated 3/5],Circuit_Speeds[CPE IP],WAN_Utilization[IP])>0,LOOKUPVALUE(Circuit_Speeds[BW/Speed Validated 3/5],Circuit_Speeds[CPE IP],WAN_Utilization[IP]),WAN_Utilization[ifHighSpeed])

Circuit speeds is a spreadsheet and WAN_Utilization is another spreadsheet. I believe there is a relationship betwen CPE IP and the IP attributes within the two different tables.

Any advice on replicating this exact calculation in POWER BI? It is supposed to calculate a new column. 

Thanks.

[v-xicai]

Hi @vacuesta ,

You can create column like DAX below.

Column1 =
VAR d =
    CALCULATE (
        FIRSTNONBLANK ( Circuit_Speeds[BW/Speed Validated 3/5], 1 ),
        FILTER (
            ALLSELECTED ( Circuit_Speeds ),
            Circuit_Speeds[CPE IP] = WAN_Utilization[IP]
        )
    )
RETURN
    IF ( d > 0, d, WAN_Utilization[ifHighSpeed] )

Best Regards,

Amy

Community Support Team _ Amy

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

[vacuesta]

I tried these 2 which are relatively the same:

= VAR d = CALCULATE(FIRSTNONBLANK(CID[BW/Speed Validated 3/5],1),FILTER(ALLSELECTED(CID), CID[CPE IP]=interface_list[ip])) RETURN IF(d>0, d, interface_list[ifHighSpeed])

=IF(LOOKUPVALUE(CID[BW/Speed Validated 3/5],CID[CPE IP],interface_list[ip])>0,LOOKUPVALUE(CID[BW/Speed Validated 3/5],CID[CPE IP],interface_list[ip]),interface_list[ifHighSpeed])

I am getting an error for both saying: "DAX xomparison operations do not support comparing values of type Text with values of type inteer. Consider using the VALUE or FORMAT function to convert one of the values."

For a bit of background info the 'ifHighSpeed' and ' BW/Speed Validated 3/5' is a numeric value; however, 'CPE IP' and 'IP' are string IP addresses. The number that is returned per row is a Numberic value though. 

Do you recommend how to go about this issue?

[vacuesta]

Update: I have the 2 tables already fed into Power BI desktop with a relationship between the two tables for CPE IP and IP. I needed advice and how to go forth with calculating this column with this calculation from excel.