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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ani26
Helper III
Helper III

Change calculated table dynamically on slicer selection

Hi Team,

 

Need some assistance. I have a source table as below.

SourceAttributeValueDate
abCMay BIT 200.11/5/2020
abCMay AUC 200.231/5/2020
aaaMay BIT 202.51/5/2020
aaaMay AUC 203.21/5/2020
utcApr BIT 190.71/4/2019
utcApr AUC 192.71/4/2019
gmtMay BIT 190.71/5/2019
gmtMay AUC 191.71/5/2019
pmtJun AUC 1921/6/2019
pmtJun BIT 1921/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:

DateMonth-YearAttribute_Bit
5/1/2020 0:00May-20May BIT 20
5/2/2020 0:00May-20May 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. 

SourceDateAUCBITBracket
abC1/5/20200.230.10-1
aaa1/5/20203.22.52-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

SourceDateAUCBITBracket
utc1/4/20192.70.70-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. 

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

v-xuding-msft
Community Support
Community Support

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

1.PNG

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-xuding-msft
Community Support
Community Support

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

1.PNG

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)
    )
DVA496_0-1691422451398.png

 



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

MFelix
Super User
Super User

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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