Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Create Dynamic Measure Tables using Values from Slicer

I have a Dynamic Measure Table and need to pass in the slicer value. Unfortunately the value of the slicer is not being passed into it. I tried creating a measure to select value from the slicer and input into the dynamic table and still no luck. 

 

The output of the Dynamic table is displayed via a Table Visualization. I tested the Year and Month Measures in cards and they are fine.

 

Has anyone come across this issue before?

 

Selected Year = SELECTEDVALUE('Date'[Year])
Selected Month = SELECTEDVALUE('Date'[Monthnumber])
TestMetricsTable = 

VAR selectedyear = [Selected Year]
VAR selectedmonth = [Selected Month]

VAR DataTempTable =
        DATATABLE ( "Category", STRING, "Year", INTEGER, { { "none", 0 } } ) //

RETURN
SELECTCOLUMNS (
    DataTempTable,
    "Category", "Selected Date",
    "Year", selectedyear,
    "Month", selectedmonth
)

 

Dynamic
measure
tables

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Is your TestMetricsTable a calculation table? In power bi it is not possible to subject the calculation table to slicers, the calculation table is only calculated during data loading and refreshing. If you need to take slicers and filters into account, you need to use measure.

     

    You can put the corresponding fields directly in the table and they will be dynamically transformed according to the selected values of the slicer.

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous, Thanks for the reply. I have a different example to make it more clearer. The Below DAX script  works fine if I use hardcoded date. But  wont work when I use the SelectedValue('Date'[Year])  & SelectedValue('Date'[Month]) 

     

    The PaxTable is a DAX Table generated Dynamically.

     

    PaxTable = 
	VAR SelectedYear = 2024
	VAR SelectedMonth = 2

	//Create the calculated Table
	VAR FilteredPAXTable =
	CALCULATETABLE(
		Staging_Table,
		YEAR(Staging_Table[TravelDate]) = SelectedYear,
		MONTH(Staging_Table[TravelDate]) = SelectedMonth
	)
	
	
	VAR TotalActuals =
	CALCULATE(
		[Actuals],
		FilteredPAXTable
	)
	
		VAR DataTempTable =
	DATATABLE(
	"Category",STRING,"Actuals",INTEGER,
	{{"none",0}}
	)
	VAR TempTable =

		SELECTCOLUMNS(
			DataTempTable,
			"Category", "Total Passengers",
			"Actuals", TotalActuals)
	
	RETURN PaxTable

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

      As mentioned in previous replies, the slicer cannot affect the calculation table. The Calculation Columns and Calculation Tables will be calculated once when the data is first loaded and will be static until the data is refreshed. If you want the calculations to depend on the slicer, you need Measure, not the calculation columns or calculation tables.

       

      Best Regards,

      Clara Gong

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks Clara. What is the solution that you would recommend. I have around 20 measures which are organized into 10 rows and 2 columns. Please note all the data that were used are from different tables which cannot be joined or related. It needs to be put in a single table vizualisation. The only way I can think of is to add create a DAX table and organize them. But slicers wont work there. Few people would have same kind of issue with the limitation on PowerBI to create Temp Tables and use the measure from Slicer filters.