Condition formula to return a value from a different column

[leecity215]

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:

Tower	Date	Count
Tech Services	Jan-24	500
Tech Services	Feb-24	575
Tech Services	Mar-24	650
Tech Services	Apr-24	725
Tech Services	May-24	800
Tech Services	Jun-24	875
Tech Services	Jul-24	950
Tech Services	Aug-24	1025
Tech Services	Sep-24	1100
Tech Services	Oct-24	1175
Tech Services	Nov-24	1250
Tech Services	Dec-24	1325

Table B:

Contract Name	Tower	Date	ME Hours	Lot Type	Unit Numbers	Max Integrations
Integration_Management_Services	Tech Services	01/01/2024	125	Incidents	600	500
Integration_Management_Services	Tech Services	01/01/2024	188	Incidents	900	750
Integration_Management_Services	Tech Services	01/01/2024	250	Incidents	1380	1000
Integration_Management_Services	Tech Services	01/01/2024	313	Incidents	1875	1250
Integration_Management_Services	Tech Services	01/01/2024	375	Incidents	2250	1500

[Wilson_]

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]

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. 

[v-yiruan-msft]

@Wilson_ Thanks for your contribution on this thread.

Hi @leecity215 ,

It will return the expected result after creating the calculated column in table A which provide by @Wilson_ , please check the below screenshot. What do you mean that it only returned a result for one row of data, the first row? Could you please provide more sample data and expected result with the logic and specific examples? It is better if you can provide the sample pbix file(exclude the sensitive info) just as suggested by @Wilson_ . Thank you.

How to upload PBI in Community

Best Regards

[Wilson_]

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. 🙂