Reply
619SK
Helper II
Helper II

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.

619SK_0-1725189168026.png

 

1 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
619SK
Helper II
Helper II

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

 

619SK
Helper II
Helper II

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

 

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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?

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

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)