Re: Can we dynamically populate a calculated table...

Anonymous · ‎07-27-2020

Hi,

Can we dynamically populate a calculated table according to the selected date range.

For Eg.

Date, Id, Name

1 Jan 2020, 1, A

2 Jan 2020, 1,A

3 Jan 2020, 1, B

4 Jan 2020, 1,B

1 Jan 2020, 2, AA

2 Jan 2020, 2,AA

3 Jan 2020, 2, BB

4 Jan 2020, 2,BC

With the above data I need to populate a dynamic table where it retrieves only the values of selected date in a slicer. Also note that my scenario doesn't allow date column as a slicer. So I need to pass this selected value as a measure which filters the dynamic table.

Regards,

Jishnu

Anonymous · ‎07-28-2020

Tables in PBI are STATIC. ALWAYS. They're recalculated when data is refreshed. Table variables used in measures are dynamic but they are gone as soon as the measure has returned its value.

Simple truth.

Anonymous · ‎07-28-2020

Thank you Daxer. I understand that this isn't possible but was looking if there is a way to implement the solution for the problem.

Anonymous · ‎07-28-2020

If you want to dynamically filter a table... just put the columns into a table visual and create a logical measure that will return 1 for the rows you want to see and 0 for those that you don't. Then use the Filter Pane to filter the rows by the measure.

Anonymous · ‎07-27-2020

Hi Harsh, Thanks for responding and sorry for not mentioning the ask correctly. I have 2 tables. Table 1 : Keeps the name of all the persons daily. Eg. Date, Id, Name Jan 1, 1, A Jan 1, 2, B Jan 2, 1, A1 Jan 2, 2, B Jan 3,1, A1 Jan 3,2, B Jan 3,3,C and Table 2 is date table from which I'm using a date slicer. I need to retain the max value of date slicer and do some filtering in the table 1. This is what I am trying: Maxdate = CALCULATE(MAX(DateDim[Date]),ALLSELECTED(DateDim[Date])) This gives me correct maxdate according to my date filter. In the Table 1 however when I try the same maxdate in a calculated column to compare the dates and flag it, it fails. The expression there is Flag = IF(Table1[Date]=[Maxdate],1,0) I found that in the Table 2s context this maxdate is the max date of entire Datedim table not that of the slicer selected. Is it because of some relationships or the DAX? It would be great if you could help me crack it. Regards, Jishnu

harshnathani · ‎07-27-2020

Hi @Anonymous ,

When you change the value of slicers , the value generated is dynamic.

You cannot pass this to a Table for a Calculated Column.

Slicers cannot be used in calculated columns. They can, however, be used in measures.

Slicers are Filters, use filter context. Filters can change dynamically based on what is selected, and how filter interactions are defined. Since measures are dynamic fields calculated on demand, they can leverage this filter context to shape results.

Then we get to calculated columns. Calculated columns use row context. They are populated once at data refresh, and don't update again. If you change interactions, change slicers, the underlying row context and any calculations performed at load would be unaffected.

https://community.powerbi.com/t5/Desktop/Using-Slicer-values-in-a-calculated-column/td-p/106354

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=31e29bfa-b116-4ca0-9970-5664de944a6e&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer#:~:text=your%20question%2C%20No.-,Slicers%20cannot%20be%20used%20in%20calculated%20columns.,how%20filter%20interactions%20are%20defined.

Regards,

Harsh Nathani