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 column like 'Date' & 'Status'.

Calculated ageing where status is Open.

 

Currently i'm calculating Points on table by using IF conditions but issue in dashboard  cant filter by control as its not dynamic.

 

Any suggestion how can i calcualte Point in power query and also in dashboard i want to creaate only 1 table of point by which user can view points data by selecting control in slicer.

 

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

9 Replies

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

    Thanks Greg_Deckler  its working , I will make few changes in my real scenerio where logic is upto 400+ count.

     

  • 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, ? 

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

    Control A 

    Result expected

    Age_CategoryOpenPoints
    0-151100
    16-300100
    31>900100
    >90280

     

     

     

    Control A data

    DateStatus
    05-05-2024Open
    05-05-2024Open
    30-08-2024Open

     

     

     

     

     

     

     

     

     

     

     

     

    Table is Append because my data is coming from 20+ sharepoint

    DateStatusControlAgeing
    05-05-2024OpenA119
    05-05-2024OpenA119
    30-08-2024OpenA1
    21-07-2024OpenB42
    22-07-2024OpenB41
    01-09-2024CloseB 
    05-08-2024OpenC27
    01-09-2024CloseC 
    01-09-2024CloseC 
    01-09-2024CloseD 
    05-08-2024OpenD27
    01-09-2024CloseD 

     

    Point logics is based upon Ageing of Open status and count of break

    No of Count0-15 day16-30 days31-90>90 days
    0 or 110010010090
    2100908080
    3908070

    60