Forum Discussion

619SK's avatar
619SK
Icon for Helper II rankHelper II
2 years ago
Solved

Power Query formula for calculating if condition and calculate count from another table

Hi I have around 20+ controls from each sharepoint, i need to calculate count with open status and give point as per ageing per the rules. i have appended all 20+ control into 1 table with two colu...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    619SK I put this solution together. Might be correct I suppose. I did some things in Power Query but the rest is DAX. See PBIX attached below signature. Really the only Power Query is unpivoting your point logic columns.

    Open = 
    VAR __AgeCategory = MAX( 'PointLogic'[Attribute] )
    VAR __Table = 
        ADDCOLUMNS(
            'Table',
            "AgeCategory",
                SWITCH( TRUE(),
                    [Ageing] < 16, "0-15",
                    [Ageing] < 31, "16-30",
                    [Ageing] < 91, "31-90",
                    ">90"
                )
        )
    VAR __Result = COUNTROWS( FILTER( __Table, [AgeCategory] = __AgeCategory ) ) + 0
RETURN
    __result




Points = 
    VAR __Open = [Open]
    VAR __AgeCategory = MAX( 'PointLogic'[Attribute] )
    VAR __Result = MAXX( FILTER( 'PointLogic', [No of Count] = __Open && [Attribute] = __AgeCategory ), [Value] )
RETURN
    __Result
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

619SK When you say "calculate Point", what do you mean exactly? Like "0-15 day", "16-30 day" or ?

619SK's avatar
619SK
Icon for Helper II rankHelper II
2 years ago

for example if number of break with open status(count) is 3 and ageing 10 then point will be 90

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    619SK So for the example table provided "Appended Table", what value goes on each row and why? And please post data as text so it can be copied and pasted. I don't understand "number of break with open status". Is that per Control, per row, ? 

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago

    Still unclear to me.

     - what do you mean by "number of break"?

     - What would your expected results be using the data you posted in your original question?

    • 619SK's avatar
      619SK
      Icon for Helper II rankHelper II
      2 years ago

      Break i mean count of open status

       

      Please find below result as seprate control wise.

      A  
      Age_CategoryOpenPoint
      0-150100
      16-300100
      31>901100
      >90190
         
      B  
      Age_CategoryOpenPoint
      0-150100
      16-300100
      31>90280
      >900100
         
      C  
      Age_CategoryOpenPoint
      0-150100
      16-301100
      31>900100
      >900100
         
      D  
      Age_CategoryOpenPoint
      0-150100
      16-301100
      31>900100
      >900100
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        619SK I put this solution together. Might be correct I suppose. I did some things in Power Query but the rest is DAX. See PBIX attached below signature. Really the only Power Query is unpivoting your point logic columns.

        Open = 
    VAR __AgeCategory = MAX( 'PointLogic'[Attribute] )
    VAR __Table = 
        ADDCOLUMNS(
            'Table',
            "AgeCategory",
                SWITCH( TRUE(),
                    [Ageing] < 16, "0-15",
                    [Ageing] < 31, "16-30",
                    [Ageing] < 91, "31-90",
                    ">90"
                )
        )
    VAR __Result = COUNTROWS( FILTER( __Table, [AgeCategory] = __AgeCategory ) ) + 0
RETURN
    __result




Points = 
    VAR __Open = [Open]
    VAR __AgeCategory = MAX( 'PointLogic'[Attribute] )
    VAR __Result = MAXX( FILTER( 'PointLogic', [No of Count] = __Open && [Attribute] = __AgeCategory ), [Value] )
RETURN
    __Result