Forum Discussion

KV2022's avatar
KV2022
Icon for Helper II rankHelper II
4 years ago
Solved

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

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...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
KV2022
Icon for Helper II rankHelper II
4 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      KV2022
      Icon for Helper II rankHelper II
      4 years ago

      thanks.I will try that