Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

add date slicer filter into summarize

Hello,

 

I have a table like this one:

 

Transformer                     DAY                 KVA%
1                                15102017                80
1                                16102017                40
1                                17102017                60
2                                15102017                40
2                                16102017                20
2                                17102017               100
3                                15102017                60
3                                16102017               100
3                                17102017               120
4                                15102017               140
4                                16102017                70
4                                17102017                90
5                                15102017                10
5                                16102017                20
5                                17102017                90

 

I created a new table, adding a new column

 

Table = ADDCOLUMNS(SUMMARIZE(Transformer,Transformer[Transformer],"Average of KVA",AVERAGE(Transformer[KVA%])),"range",SWITCH(TRUE(),[Average of KVA]>80,"KVA% >80",[Average of KVA]<=50,"KVA%<=50","50<Average of KVA%<=80"))

 

But I have a date slicer, that would like to take into account in the table created. How should I do? Should I filter the summarize by DAY of the transfomer table?

 

In addition, I would like to change the switch function to values that an user can set like a filter

 

SWITCH(TRUE(),[Average of KVA]>x,"KVA% >x",[Average of KVA]<=y,"KVA%<=y","y<Average of KVA%<=x")

How this X and Y needs to be set in the report? 

 

Thanks

 

2 REPLIES 2
Eric_Zhang
Microsoft Employee
Microsoft Employee

@Anonymous wrote:

Hello,

 

I have a table like this one:

 

Transformer                     DAY                 KVA%
1                                15102017                80
1                                16102017                40
1                                17102017                60
2                                15102017                40
2                                16102017                20
2                                17102017               100
3                                15102017                60
3                                16102017               100
3                                17102017               120
4                                15102017               140
4                                16102017                70
4                                17102017                90
5                                15102017                10
5                                16102017                20
5                                17102017                90

 

I created a new table, adding a new column

 

Table = ADDCOLUMNS(SUMMARIZE(Transformer,Transformer[Transformer],"Average of KVA",AVERAGE(Transformer[KVA%])),"range",SWITCH(TRUE(),[Average of KVA]>80,"KVA% >80",[Average of KVA]<=50,"KVA%<=50","50<Average of KVA%<=80"))

 

But I have a date slicer, that would like to take into account in the table created. How should I do? Should I filter the summarize by DAY of the transfomer table?

 

In addition, I would like to change the switch function to values that an user can set like a filter

 

SWITCH(TRUE(),[Average of KVA]>x,"KVA% >x",[Average of KVA]<=y,"KVA%<=y","y<Average of KVA%<=x")

How this X and Y needs to be set in the report? 

 

Thanks

 

@Anonymous

If you'd like the percentages in the Pie chart, it is not possible to set dynamic x&y. Because a fixed axuliary table has to be created in advance.

 

As to the date slicer, I don't get your question, could you be more specific?

Anonymous
Not applicable

Thanks.

In the sample that you made, I just created the auxiliary table attached.

sample.JPG

with a mesure COUNT = COUNTROWS(FILTER('Table','Table'[range]=MAX('Table'[range])))

I want that if I chose a day the pie chart is calculated considering the day selectionned (now calculate all days). I tried to add the day into auxiliary "Table" but it is not correct as count by day.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.