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

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
      • 619SK's avatar
        619SK
        Icon for Helper II rankHelper II
        2 years ago

        Point column total is not coming as sum of value how can I convert to summarise format