cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper V

## Need to write a calculation based on two tables with a condition

Hi

I need to create a DAX measure based on a condition

I have two tables HData, Contractdata where i need to calcuate count(UniqueID) based on a condition (> ) with two differnt tables.

How to write the below conditions with two differnt tables

CALCULATE(count(Hdata[uniqeID]),
FILTER(Hdata,Hdata[Duration]) >
(FILTER('Contractdata','Contract Type'[SERVICE_LEVEL_TARGET])))

How to write the above one?, I'm getting error.

Thanks,
Rams
1 ACCEPTED SOLUTION
Community Support

You can create a measure as below:

``````COUNTID =
CALCULATE (
COUNT ( 'TABLEA'[UniqueID] ),
FILTER (
'TABLEA',
'TABLEA'[Duration]
> CALCULATE (
SUM ( 'TABLEB'[Duration] ),
FILTER ( 'TABLEB', 'TABLEB'[Id] = EARLIER ( 'TABLEA'[UniqueID] ) )
)
)
)``````

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

Does not seem right. Can you share sample data and sample output.

Helper V

Hi please find the below screenshot for requirment.

i need to create a measures as below ;;

Unique id count =

calculate(

count(tablea[uniqueid),

filter( tablea(duration)    > tableb(duration)

)

I'm unable to write the DAX , i'm aware of wirting of DAX with if two columns available in same table then we can write as below

COUNTID =

CALCULATE

(COUNT(TABLEA[UNIQUEID],

FILTER(TABLEA, TABLEA[DURATION1] > TABLEA[DURATION2]

)

but in my case the condtion which i ned to write each colum is avaialbe in differnt table and need to write as

Based on this condtion tablea(duration) > tableb(duration) i need to calculate count in one table.

Community Support

You can create a measure as below:

``````COUNTID =
CALCULATE (
COUNT ( 'TABLEA'[UniqueID] ),
FILTER (
'TABLEA',
'TABLEA'[Duration]
> CALCULATE (
SUM ( 'TABLEB'[Duration] ),
FILTER ( 'TABLEB', 'TABLEB'[Id] = EARLIER ( 'TABLEA'[UniqueID] ) )
)
)
)``````

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors