- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @Greg_Deckler its working , I will make few changes in my real scenerio where logic is upto 400+ count.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
|
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
for example if number of break with open status(count) is 3 and ageing 10 then point will be 90
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Point column total is not coming as sum of value how can I convert to summarise format
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
11-20-2023 02:17 AM | |||
Anonymous
| 07-25-2023 06:32 AM | ||
06-19-2024 01:24 AM | |||
12-13-2023 10:03 AM | |||
12-13-2023 06:38 AM |