Forum Discussion

leecity215's avatar
leecity215
New Member
2 years ago

Condition formula to return a value from a different column

Hi everyone,

 

PowerBI newb here as you are about to see.

 

In my model data mode i have two seperate tables (shown below).

 

What i am trying to do is based on the column "Count" in Table A return results from Table B "ME Hours" and "Unit Numbers" based on the "Count" column and where the result from a given row sits in the "Max Integrations" column.

 

For example, May' 24 "Count" is 800. I would like to create a measure that returns 250 ME hours from Table B, as my value from Table A is above 750 Intregations but below 1000. 

 

Any guidance you can provide on how best to structure this would be most appreciated.

Thanks

 

Lee

 

Table A:

TowerDateCount
Tech ServicesJan-24500
Tech ServicesFeb-24575
Tech ServicesMar-24650
Tech ServicesApr-24725
Tech ServicesMay-24800
Tech ServicesJun-24875
Tech ServicesJul-24950
Tech ServicesAug-241025
Tech ServicesSep-241100
Tech ServicesOct-241175
Tech ServicesNov-241250
Tech ServicesDec-241325

 

Table B:

Contract NameTowerDateME HoursLot TypeUnit NumbersMax Integrations
Integration_Management_ServicesTech Services01/01/2024 125Incidents600500
Integration_Management_ServicesTech Services01/01/2024188Incidents900750
Integration_Management_ServicesTech Services01/01/2024250Incidents13801000
Integration_Management_ServicesTech Services01/01/2024313Incidents18751250
Integration_Management_ServicesTech Services01/01/2024375Incidents22501500
Need Help

4 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi leecity215,

     

    If you're trying to add a calculated column to TableA, try:

    ME Hours =
VAR Filtered =
FILTER (
    TableB,
    TableB[Max Integrations] >= TableA[Count]
)
VAR Result =
MINX (
    Filtered,
    TableB[ME Hours]
)

RETURN Result

     

    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

     

    P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

    • leecity215's avatar
      leecity215
      New Member
      2 years ago

      Hi there, this worked really well. The only issue is that it only returned a result for one row of data, the first row? Could it be because in table B in my model i am just showing the year whereas in table i am showing a long dat format dd/mm/yyyy? Thank you very much for your help. 

      • Wilson_'s avatar
        Wilson_
        Icon for Memorable Member rankMemorable Member
        2 years ago

        leecity,

         

        Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂