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 to be a measure.

 

thanks

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 

5 Replies

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.