Forum Discussion

619SK's avatar
619SK
Helper 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
ronrsnfld's avatar
ronrsnfld
Super 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
Helper 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
    Community 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
      Helper II
      2 years ago

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