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

[619SK]

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.

[619SK]

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

[619SK]

Control A 

Result expected

Age_Category	Open	Points
0-15	1	100
16-30	0	100
31>90	0	100
>90	2	80

Control A data

Date	Status
05-05-2024	Open
05-05-2024	Open
30-08-2024	Open

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

Date	Status	Control	Ageing
05-05-2024	Open	A	119
05-05-2024	Open	A	119
30-08-2024	Open	A	1
21-07-2024	Open	B	42
22-07-2024	Open	B	41
01-09-2024	Close	B
05-08-2024	Open	C	27
01-09-2024	Close	C
01-09-2024	Close	C
01-09-2024	Close	D
05-08-2024	Open	D	27
01-09-2024	Close	D

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

No of Count	0-15 day	16-30 days	31-90	>90 days
0 or 1	100	100	100	90
2	100	90	80	80
3	90	80	70	60

[Greg_Deckler]

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

[619SK]

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

[ronrsnfld]

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]

Break i mean count of open status

Please find below result as seprate control wise.

A
Age_Category	Open	Point
0-15	0	100
16-30	0	100
31>90	1	100
>90	1	90
B
Age_Category	Open	Point
0-15	0	100
16-30	0	100
31>90	2	80
>90	0	100
C
Age_Category	Open	Point
0-15	0	100
16-30	1	100
31>90	0	100
>90	0	100
D
Age_Category	Open	Point
0-15	0	100
16-30	1	100
31>90	0	100
>90	0	100

[Greg_Deckler · Accepted solution]

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

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

[Greg_Deckler]

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