Forum Discussion
Dynamic filter and dynamic column measure
Hi Harrisfil ,
You can create a new slicer, use the date in the Table as the slicer field, and then modify the formula and use ALLSELECTED function:
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Harrisfil ,
I created simple data:
Please follow the steps:
1.In Power Query, click "Reference" and remove columns:
2.Add a step and close Power Query Editor:
= Table.Distinct( Table.InsertRows( #"Removed Columns",1,{[TopN="RestOfcountries"]}))
3.Create a replationship:
4.Create a measure:
Measure2 =
VAR _table = TOPN(SELECTEDVALUE(Parameter[Parameter]),SUMMARIZE(ALL('Table'),'Table'[TopN],'Table'[Date],"Sales",[Measure]),[Sales])
VAR _top_country = SELECTCOLUMNS(_table,'Table'[TopN])
VAR _other_country = EXCEPT(ALL('Table'[TopN]),_top_country)
VAR _other_country_sales = SUMX(FILTER(ALL('Table'),'Table'[TopN] in _other_country),'Table'[Sales])
RETURN SWITCH(TRUE(),
SELECTEDVALUE('Table (2)'[TopN]) IN _top_country,MAXX(FILTER(_table,'Table'[TopN]=SELECTEDVALUE('Table (2)'[TopN])),[Sales]),
SELECTEDVALUE('Table (2)'[TopN]) = "RestOfcountries",_other_country_sales
)
5.The result is as follows:
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for your time on that! Indeed its working great 🙂 Can i somehow add a date filter as well? so i can have both parameter and date filter in my model.
thank you again
Hi Harrisfil ,
You can create a new slicer, use the date in the Table as the slicer field, and then modify the formula and use ALLSELECTED function:
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
great thank you very much ! its working great.
something last : can i always set Rest of countries row in the end of the matrxi table ? i need first to sort it by the measure2 but i need this specific one to be sorted in the end indepedently of the measure asc/desc sorting in the table
