Forum Discussion

sjoshi's avatar
sjoshi
Helper II
7 years ago

IF, FILTER SUM Function

Hello,   I'm trying to do one simple thing but not sure why filter is not working   If accountnum ="14850" the take sum of amount for accountnum "52500" else null. This statement I have tried to ...
Need Help
Agreenwood's avatar
Agreenwood
Helper I
7 years ago
It's always going to return 0 because you are stating where account num = 14850 then sum where account number = 52500. You can't be both 14850 and 52500 at the same time.
  • sjoshi's avatar
    sjoshi
    Helper II
    7 years ago

    Hello

    Yes I have modified the mesure and column to get the account number from the dimension table as below but still the values is zero. 

     

    52500 = IF(HASONEVALUE('LedgerTable Dim'[Accountnum]),IF(VALUES('LedgerTable Dim'[Accountnum])="14850",CALCULATE(SUM('LedgerTrans Fact'[AmountMST]),'LedgerTable Dim'[Accountnum]="52500"),0),BLANK())

     


    52500Col = IF(RELATED('LedgerTable Dim'[Accountnum])="14850",CALCULATE (
    SUM ( 'LedgerTrans Fact'[AmountMST] ),
    FILTER (ALL( 'LedgerTrans Fact'), RELATED('LedgerTable Dim'[Accountnum]) = "52500" )
    ),0)

     

     

     

     

    Thanks

    Smita

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

       

      I am out of ideas now.

    • sjoshi's avatar
      sjoshi
      Helper II
      7 years ago

      Hello

       

      To explain more

      In Account slicer we are selecting account number 14800. This Account slicer value is coming from Ledgertable dimension. LedgerTransFact.Accountnum (fact table) =Ledgertable.accountnum (Dimension table).  

      When the slicer value is 14800 then get the sum of amount from ledgertransfact table for account num 52000. I have tried below way also still it is giving zero value not sure what is incorrect 

       

      52500 = IF(HASONEVALUE('LedgerTable Dim'[Accountnum]),IF(VALUES('LedgerTable Dim'[Accountnum])="14850",CALCULATE(SUM('LedgerTrans Fact'[AmountMST]),'LedgerTrans Fact'[ACCOUNTNUM]="52500"),0),BLANK())

       

       

      52500Col = IF(RELATED('LedgerTable Dim'[Accountnum])="14850",
      CALCULATE (
      SUM ( 'LedgerTrans Fact'[AmountMST] ),
      FILTER (ALL( 'LedgerTrans Fact'), 'LedgerTrans Fact'[ACCOUNTNUM] = "52500" )
      ),0)

       

       

      • sjoshi's avatar
        sjoshi
        Helper II
        7 years ago

        Hello

         

        After further analysis I saw that yes fact table account number and the dimension (Slicer Account) accountnum values are same for given record thats why sum is coming zero all the time in power BI but still I need to achieve that If statement. Is there any way to do that?

         

        If LedgertableDimension.accountnum = "14850" then take sum amountmst for LedgertransFact.accountnum="52500" else null

         

        Slicer: LedgertableDimension.accountnum

         

        Thanks

        Smita