Forum Discussion

vacuesta's avatar
vacuesta
Helper I
6 years ago

Replicate a Calculation from Excel in PowerBI

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.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

3 Replies

  • vacuesta's avatar
    vacuesta
    Helper I
    6 years ago

    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.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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's avatar
      vacuesta
      Helper I
      6 years ago

      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?