Hello!I am having a hard time finding the correct logic to use for an issue I have filtering a table. The table is a reference table with values that change over time (i.e. electricity rates that change every few months). The goal of this script is to filter the reference table to a single row, which I will then reference for a calculated column. Here is the source data I am using:UTILITYEFFECTIVE_DATERATESUMMER_CONSUMPTION_ON_PEAKSCE6/1/21TOU-GS-3E0.50727SCE6/1/21TOU-GS-3D0.13061SCE6/1/21TOU-GS-2-E0.55612SCE6/1/21TOU-GS-2-D0.13828SCE6/1/21TOU-GS-1-D0.16903SCE6/1/21TOU-GS-1-E0.4701SCE6/1/21AL-2F0.0873SCE6/1/21TOU-GS-3E-CPP0.50727SCE6/1/21TOU-GS-3D-CPP0.13061SCE6/1/21TOU-GS-2-E-CPP0.55612SCE6/1/21TOU-GS-2-D-CPP0.13828SCE6/1/21TOU-GS-1-D-CPP0.16903SCE6/1/21TOU-GS-1-E-CPP0.4701SCE2/1/21TOU-GS-3E0.50521SCE2/1/21TOU-GS-3D0.12889SCE2/1/21TOU-GS-2-E0.55352SCE2/1/21TOU-GS-2-D0.13618SCE2/1/21TOU-GS-1-D0.16687SCE2/1/21TOU-GS-1-E0.4676SCE2/1/21AL-2F0.08543SCE2/1/21TOU-GS-3E-CPP0.50521SCE2/1/21TOU-GS-3D-CPP0.12889SCE2/1/21TOU-GS-2-E-CPP0.55352SCE2/1/21TOU-GS-2-D-CPP0.13618SCE2/1/21TOU-GS-1-D-CPP0.16687SCE2/1/21TOU-GS-1-E-CPP0.4676SCE10/1/20TOU-GS-3E0.48829SCE10/1/20TOU-GS-3D0.12473SCE10/1/20TOU-GS-2-E0.5405SCE10/1/20TOU-GS-2-D0.13279SCE10/1/20TOU-GS-1-D0.15859SCE10/1/20TOU-GS-1-E0.44951SCE10/1/20AL-2F0.08513 My confusion lies in finding a way to perform a TOPN function on an already filtered table. Any insight on how to "nest" a filtered table in a TOPN filter?What I trying to accomplish:1. Filter by RATE column FILTER(Sheet1, Sheet1[RATE] = "TO-GS-2-D") 2. Filter EFFECTIVE_DATE using logical operator FILTER(Sheet1, Sheet1[EFFECTIVE_DATE] < 'REFERENCE_DATE') 3. Return the the latest row TOPN(1,Sheet1, Sheet1[EFFECTIVE_DATE], DESC) Looking to output the SUMMER_CONSUMPTION_ON_PEAK value as a measure. Any insight is much appreciated!

MTBERRY1029 not sure if this is what you are looking for, see attached, tweak as you see fit. ✨ Follow us on LinkedIn Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation! ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

MTBERRY1029

Regular Visitor

5 years ago

Solved

Performing TOPN on filtered table

Hello!

I am having a hard time finding the correct logic to use for an issue I have filtering a table. The table is a reference table with values that change over time (i.e. electricity rates that change every few months). The goal of this script is to filter the reference table to a single row, which I will then reference for a calculated column. Here is the source data I am using:

UTILITY	EFFECTIVE_DATE	RATE	SUMMER_CONSUMPTION_ON_PEAK
SCE	6/1/21	TOU-GS-3E	0.50727
SCE	6/1/21	TOU-GS-3D	0.13061
SCE	6/1/21	TOU-GS-2-E	0.55612
SCE	6/1/21	TOU-GS-2-D	0.13828
SCE	6/1/21	TOU-GS-1-D	0.16903
SCE	6/1/21	TOU-GS-1-E	0.4701
SCE	6/1/21	AL-2F	0.0873
SCE	6/1/21	TOU-GS-3E-CPP	0.50727
SCE	6/1/21	TOU-GS-3D-CPP	0.13061
SCE	6/1/21	TOU-GS-2-E-CPP	0.55612
SCE	6/1/21	TOU-GS-2-D-CPP	0.13828
SCE	6/1/21	TOU-GS-1-D-CPP	0.16903
SCE	6/1/21	TOU-GS-1-E-CPP	0.4701
SCE	2/1/21	TOU-GS-3E	0.50521
SCE	2/1/21	TOU-GS-3D	0.12889
SCE	2/1/21	TOU-GS-2-E	0.55352
SCE	2/1/21	TOU-GS-2-D	0.13618
SCE	2/1/21	TOU-GS-1-D	0.16687
SCE	2/1/21	TOU-GS-1-E	0.4676
SCE	2/1/21	AL-2F	0.08543
SCE	2/1/21	TOU-GS-3E-CPP	0.50521
SCE	2/1/21	TOU-GS-3D-CPP	0.12889
SCE	2/1/21	TOU-GS-2-E-CPP	0.55352
SCE	2/1/21	TOU-GS-2-D-CPP	0.13618
SCE	2/1/21	TOU-GS-1-D-CPP	0.16687
SCE	2/1/21	TOU-GS-1-E-CPP	0.4676
SCE	10/1/20	TOU-GS-3E	0.48829
SCE	10/1/20	TOU-GS-3D	0.12473
SCE	10/1/20	TOU-GS-2-E	0.5405
SCE	10/1/20	TOU-GS-2-D	0.13279
SCE	10/1/20	TOU-GS-1-D	0.15859
SCE	10/1/20	TOU-GS-1-E	0.44951
SCE	10/1/20	AL-2F	0.08513

My confusion lies in finding a way to perform a TOPN function on an already filtered table. Any insight on how to "nest" a filtered table in a TOPN filter?

What I trying to accomplish:

1. Filter by RATE column

FILTER(Sheet1, Sheet1[RATE] = "TO-GS-2-D")

2. Filter EFFECTIVE_DATE using logical operator

FILTER(Sheet1, Sheet1[EFFECTIVE_DATE] < 'REFERENCE_DATE')

3. Return the the latest row

TOPN(1,Sheet1, Sheet1[EFFECTIVE_DATE], DESC)

Looking to output the SUMMER_CONSUMPTION_ON_PEAK value as a measure.

Any insight is much appreciated!

Need Help

parry2k
5 years ago
MTBERRY1029 not sure if this is what you are looking for, see attached, tweak as you see fit.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
rate max consumption.pbix51 KB
Ashish_Mathur
5 years ago
Hi,

You may download my PBI file from here.

Hope this helps.

6 Replies

parry2k
Super User
5 years ago
MTBERRY1029 not sure if this is what you are looking for, see attached, tweak as you see fit.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
rate max consumption.pbix51 KB
parry2k
Super User
5 years ago
MTBERRY1029 instead of pasting the image, can you paste the data in a table format or share pbix file and also share what is your expected output.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- MTBERRY1029
  Regular Visitor
  5 years ago
  Updated. Does that help?
parry2k
Super User
5 years ago
MTBERRY1029 what is the reference date? In the above example what would be the output?
- MTBERRY1029
  Regular Visitor
  5 years ago
  For example :
  REFERENCE_DATE = 4/10/21
  
  OUTPUT: 0.13618
  - Ashish_Mathur
    Super User
    5 years ago
    Hi,
    
    You may download my PBI file from here.
    
    Hope this helps.

Forum Discussion

Performing TOPN on filtered table

6 Replies

Related content

Filtered table

Can I filter the 1st fact table which then filters a 2nd fact table?

DAX Performance

DAX performance optimization

Filtering Table report