Change calculated table dynamically on slicer selection

[Ani26]

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. 

[MFelix · Accepted solution]

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?

[v-xuding-msft · Accepted solution]

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 )
    )

[tmarcus]

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?

[MFelix]

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. 

[DVA496]

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)

RETURN

     CALCULATE(

        SUM('öGIG'[CF]),

        FILTER(Dates, Dates[Dates] < [S] && X <>1000)

    )

[Ani26]

Thank you @v-xuding-msft  and @MFelix . Your solutions made it work. 🙂

[MFelix · Accepted solution]

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?