Forum Discussion
Change calculated table dynamically on slicer selection
Hi Team,
Need some assistance. I have a source table as below.
| Source | Attribute | Value | Date |
| abC | May BIT 20 | 0.1 | 1/5/2020 |
| abC | May AUC 20 | 0.23 | 1/5/2020 |
| aaa | May BIT 20 | 2.5 | 1/5/2020 |
| aaa | May AUC 20 | 3.2 | 1/5/2020 |
| utc | Apr BIT 19 | 0.7 | 1/4/2019 |
| utc | Apr AUC 19 | 2.7 | 1/4/2019 |
| gmt | May BIT 19 | 0.7 | 1/5/2019 |
| gmt | May AUC 19 | 1.7 | 1/5/2019 |
| pmt | Jun AUC 19 | 2 | 1/6/2019 |
| pmt | Jun BIT 19 | 2 | 1/6/2019 |
The requirement is, I need to create buckets on the values column for the Attribute field where the value contains BIT.
Let me explain further.
There is a slicer on my page which has the field 'Month-Year' (values are like May-20, Apr-20, Mar-20 and so on) dragged into it from the Date dimension. The Date dimension also has a column Attribute_Bit.
DATE:
| Date | Month-Year | Attribute_Bit |
| 5/1/2020 0:00 | May-20 | May BIT 20 |
| 5/2/2020 0:00 | May-20 | May BIT 20 |
The Date dimension and Source table are connected to each other on Date field.
The requirement is when a user selects 'Month-Year' on the slicer (say value is May-20), I need to have a calculated table fetching the Source column, Date column, AUC and BIT like below. The column Bracket will be a calculated column based on the BIT column.
| Source | Date | AUC | BIT | Bracket |
| abC | 1/5/2020 | 0.23 | 0.1 | 0-1 |
| aaa | 1/5/2020 | 3.2 | 2.5 | 2-3 |
I tried to create the same by using SUMMARIZE function and the table gets created. No issues uptil here.
The thing is when the slicer value is changed to say Apr-20 then the table should show the Apr values like below
| Source | Date | AUC | BIT | Bracket |
| utc | 1/4/2019 | 2.7 | 0.7 | 0-1 |
The values dont change. I tried to create a realationship between this new calculated table and the Date dimension but it gives me a circular dependency error.
DAX used for creating calculated table:
CAL_TAB =
Var auc = [AUCMonth_N]
Var bit = [BITMonth_N]
RETURN
SUMMARIZE(SourceT,SourceT[Source],"Month",CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Attribute_Bit] = bit)),"AUC",CALCULATE(SourceT,FILTER(SourceT,SourceT[Attribute] = auc)),
"BIT",CALCULATE(SUM(SourceT[Value]),FILTER(SourceT,SourceT[Attribute] = bit))
)
var auc and bit are so created that if the Month-Year is selected as May-20 then auc = May AUC 20 and bit = May BIT 20.
Request you to kindly assist me with the same.
Hi Ani26 ,
Calculated tables cannot be affect dinamically by slicers, so you cannot have a dinamic calculted table on the fly to use on a visualization, however you can create a measure that makes use of a calculated table (as a variable) and then make it dinamic with the use of slicers.
I was looking at your example and didn't understood what you want to create is the Bracket part or only the filtering of the data by bit?
Hi Ani26 ,
As MFelix said, it can't create a calculated table dynamically based on slicers. But you can do it in measures. Here is my sample that you could have a try.
Month-Year = DISTINCT('Date'[Month-Year])Bracket = VAR X = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Date', 'Date'[Month-Year] = [S] ) ) RETURN SWITCH ( TRUE (), X > 0 && X < 1, "0-1", X >= 1 && X < 2, "1-2", x > 2, ">2", BLANK () )BIT = VAR BIT = SEARCH ( "BIT", MAX ( 'Table'[Attribute] ),, 1000 ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Date', 'Date'[Month-Year] = [S] && BIT <> 1000 ) )
6 Replies
Hi Ani26 ,
Calculated tables cannot be affect dinamically by slicers, so you cannot have a dinamic calculted table on the fly to use on a visualization, however you can create a measure that makes use of a calculated table (as a variable) and then make it dinamic with the use of slicers.
I was looking at your example and didn't understood what you want to create is the Bracket part or only the filtering of the data by bit?
Hi Ani26 ,
As MFelix said, it can't create a calculated table dynamically based on slicers. But you can do it in measures. Here is my sample that you could have a try.
Month-Year = DISTINCT('Date'[Month-Year])Bracket = VAR X = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Date', 'Date'[Month-Year] = [S] ) ) RETURN SWITCH ( TRUE (), X > 0 && X < 1, "0-1", X >= 1 && X < 2, "1-2", x > 2, ">2", BLANK () )BIT = VAR BIT = SEARCH ( "BIT", MAX ( 'Table'[Attribute] ),, 1000 ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Date', 'Date'[Month-Year] = [S] && BIT <> 1000 ) )Thank you v-xuding-msft and MFelix . Your solutions made it work. 🙂
Hello,
Im trying to do something similar by using the date selected as a cutoff date but get the values in the new column same as the original vlaues with the corresponding date if the date is before the cutoff one and zero if the date is after cutoff date. I create an additional column called B&S. When I select 31.12.2024 for example I get the values from 2023 but not the ones from 2019, 2020... Can you help me?
B&S =VAR X =SEARCH ("öGIG", MAX('öGIG'[Asset]),,1000)RETURNCALCULATE(SUM('öGIG'[CF]),FILTER(Dates, Dates[Dates] < [S] && X <>1000))What if you want to reference this table multiple times for downstream measures and or build other columns from the table rows? Surely it isn't efficient to define this table in every measure you want to create. Is there not another way to do this in PowerBi?
Hi tmarcus,
Depends on the end result you may used calculation groups or other options to make this work, however the problem that you describe also is the same for calculated tables that that would be usong those slicer options. Currently you also have visual calculations that can be added to that a specific visual.
Context is very important.
