Forum Discussion

Harrisfil's avatar
Harrisfil
Helper I
1 year ago
Solved

Dynamic filter and dynamic column measure

hello ,

 

I have the following issue. I have a small table:

 

TopNMeasureADate
Greece101/5/2024
Spain156/8/2024
Singapore2215/7/2024
Jamaica471/4/20243

what i  need is to create a parameter filter where i will filter this table per Top2 or Top3

 

and i need this table to be changed as follows: 

 

if i select top 2 then i need to see the top 2 countries perYtd and an extra row named " RestofCountries" with the rest total of other unfiltered countries. Any idea? thank you

 

TopNMeasureADate
Singapore2215/7/2024
Jamaica471/4/20243
RestOfcountries25 

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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 Team

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • Harrisfil's avatar
      Harrisfil
      Helper I
      1 year ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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 Team

         

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