difference between to fact table columns based on conditions involving a fact and dim table

[KV2022]

Hi , am new to power bi. can anyone help with this?

I need to find a difference between two date columns in fact table based on a flag in the fact table and one column in dimension table. This needs to be a measure.

thanks

[v-yalanwu-msft]

Hi, @KV2022 ;

You could try to create this measure.

diff =
SUM ( 'fact'[flag] ) - SUM ( 'dimension'[column] )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@KV2022 , If both dates are in Fact, use related(Dim[Date]) if date is in dimension

Calculate(Sumx(Table, Datediff([Date1], [Date2], day) , filter(Table,Table[Col] = "ABC"), Filter(Dim, Dim[Col] ="ABC") )

You can use Averagex in place of sumx 

[KV2022]

thanks.

But the dates are in fact table and so is the flag.  Something like this 

fact(actual end date)- fact(actual start date)  on fact(flag) = value and dim(column) = value. 

it needs to be a measure

[amitchandak]

@KV2022 , If values are coming from the slicer, you do not need even add filter code

Also, I suggested a measure only

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[KV2022]

thanks.I will try that