Forum Discussion

docdata's avatar
docdata
Frequent Visitor
1 year ago
Solved

Tooltip with RANKX over years

A table visual with a Country, Announcements, Investment, and Jobs columns has a custom tooltip.  In the tooltip, I'm trying to plot a country's rank (jobs and investment) over time.   Here are var...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,docdata .Thank you for your reply.
    It looks like you have successfully implemented the first step of giving the rankx the correct computational environment (computational table range), you now need to modify the filters in your MEASUREMENT according to your actual sorting needs.
    I have looked at your two ways of writing the sort and here is my analysis of the results of their implementation:
    Your first Use the ALLEXCEPT writeup:

     ALLEXCEPT(
      MergedOppsAnns,
      MergedOppsAnns[new_announcement.1.new_date].[Year]
    )

    ALLEXCEPT function: removes all filters except year.
    CALCULATE function: calculates the total number of jobs for each country in a given year
    RANKX function: the total number of jobs calculated in descending order, using dense ranking

    In short, your current computing environment has only a year as a filter, and not any other filter (the ranking results will only be affected by changes in the relevant year field)

    Your second writeup using SUMMARIZECOLUMNS

    generates a summary table, and I notice that you are not using any filters, similar to using the ALL() function directly.
    You can try adding more filters to your table constraints. For example, if you only want to see data for a specific country.

    like this:

    EVALUATE
  SUMMARIZECOLUMNS(
    MergedOppsAnns[new_internationalcountyname],
    MergedOppsAnns[new_announcement.1.new_date].[Year],
    FILTER(
      MergedOppsAnns,
      MergedOppsAnns[new_internationalcountyname] = "Specific Country"
    ),
    "Jobs", CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_jobs])),
    "Country Jobs Rank", 'MergedOppsAnns'[Country Jobs Rank],
    "Investment", CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_investments])),
    "Investment Rank", [Country Investment Rank]
  )

    You can add other filter functions to it to make it dynamically sorted

    If you want the ranking to target specific countries or other dimensions, you can adjust the ALLEXCEPT function in RANKX. For example, keep the country and year filters:

     countryYear_Rankx=
 RANKX(
    ALLEXCEPT(
      MergedOppsAnns,
      MergedOppsAnns[new_announcement.1.new_date].[Year],
      MergedOppsAnns[new_internationalcountyname]
    ),
    CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_jobs])),
    ,
    DESC,
    Dense
)

    Most importantly, you need to double-check the usage of the functions you use to make sure they meet your real needs and get the right sorting results!
    You can check the following links to see more filtering dax functions
    URL:

    Filter functions (DAX) - DAX | Microsoft Learn

    SUMMARIZE function (DAX) - DAX | Microsoft Learn


    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    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
1 year ago

Hi,docdata .I am glad to help you.

Based on my testing, it is possible that your first parameter in the Rankx function is set incorrectly resulting in an incorrect calculation environment.
The column [new_announcement.1.new_investments] is not present in the calculation range (table) filtered by the rankx function when sorting (the presence of this column is required for rankx calculations as a basis for sorting, and therefore it must be present in the filtered table range in the rankx function)

Your filter range.(Incorrect range)

Correct filter ranges (won't have the problem you are having)
Here the ALL function is used to remove all the filter conditions from the table
At this point in the filter table exists [new_announcement.1.new_investments] column, rankx exists the correct basis for ranking, in this calculation environment can be calculated ranking

Regardless of your sort criteria (whether you want to add slicer filtering, whether you want to take into account Countryname (group sorting based on countryname), the first parameter table of your rankx must contain the column [new_announcement.1.new_investments])


This is my modified code (they can all be sorted properly at the moment, but the logic of the code sorting you still need to modify according to your actual needs)

All_Country Investment Rank = 
  RANKX(
    ALL('MergedOppsAnns'),
    CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_investments])
    )
)
Country Investment Rank = 
  CALCULATE(
    RANKX(
      ALLSELECTED('MergedOppsAnns'),                                                         
      CALCULATE(SUM('MergedOppsAnns'[new_announcement.1.new_investments]))
    )
)



Since it is not clear what your specific sorting judgment conditions are, here are my suggestions that may help you.
1. If you want to implement group sorting based on countryname, you can consider ALLEXCEPT function
2. If you want to consider an external slicer in the measure calculation, you can consider the ALLSELECTED function
3. If you want to remove all external filters, you can consider the ALL function.
It is important to note that the calculation result of the measure is very dependent on the filtering environment, you need to set up the correct calculation environment according to your actual situation to ensure that the measure calculates the correct result.

If you can provide more information, it is best to provide test data that does not contain sensitive data, which will help you write the correct sorting code.


I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • docdata's avatar
    docdata
    Frequent Visitor
    1 year ago

    Thanks!

     

    I'm trying to rank each country within the year.  I tried

    MEASURE 'MergedOppsAnns'[Country Jobs Rank] =
  RANKX(
    ALLEXCEPT(
      MergedOppsAnns,
      MergedOppsAnns[new_announcement.1.new_date].[Year]
    ),
    CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_jobs])),
    ,
    DESC,
    Dense
)

    and 

    EVALUATE
	SUMMARIZECOLUMNS(
		MergedOppsAnns[new_internationalcountyname],
		MergedOppsAnns[new_announcement.1.new_date].[Year],
		"Jobs", CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_jobs])),
		"Country Jobs Rank", 'MergedOppsAnns'[Country Jobs Rank],
		"Investment", CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_investments])),
		"Investment Rank", [Country Investment Rank]
	)

    returns 

    (ordered by year and rank):

    So it seems to be evaluating rank over the entire table

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi,docdata .Thank you for your reply.
      It looks like you have successfully implemented the first step of giving the rankx the correct computational environment (computational table range), you now need to modify the filters in your MEASUREMENT according to your actual sorting needs.
      I have looked at your two ways of writing the sort and here is my analysis of the results of their implementation:
      Your first Use the ALLEXCEPT writeup:

       ALLEXCEPT(
      MergedOppsAnns,
      MergedOppsAnns[new_announcement.1.new_date].[Year]
    )

      ALLEXCEPT function: removes all filters except year.
      CALCULATE function: calculates the total number of jobs for each country in a given year
      RANKX function: the total number of jobs calculated in descending order, using dense ranking

      In short, your current computing environment has only a year as a filter, and not any other filter (the ranking results will only be affected by changes in the relevant year field)

      Your second writeup using SUMMARIZECOLUMNS

      generates a summary table, and I notice that you are not using any filters, similar to using the ALL() function directly.
      You can try adding more filters to your table constraints. For example, if you only want to see data for a specific country.

      like this:

      EVALUATE
  SUMMARIZECOLUMNS(
    MergedOppsAnns[new_internationalcountyname],
    MergedOppsAnns[new_announcement.1.new_date].[Year],
    FILTER(
      MergedOppsAnns,
      MergedOppsAnns[new_internationalcountyname] = "Specific Country"
    ),
    "Jobs", CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_jobs])),
    "Country Jobs Rank", 'MergedOppsAnns'[Country Jobs Rank],
    "Investment", CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_investments])),
    "Investment Rank", [Country Investment Rank]
  )

      You can add other filter functions to it to make it dynamically sorted

      If you want the ranking to target specific countries or other dimensions, you can adjust the ALLEXCEPT function in RANKX. For example, keep the country and year filters:

       countryYear_Rankx=
 RANKX(
    ALLEXCEPT(
      MergedOppsAnns,
      MergedOppsAnns[new_announcement.1.new_date].[Year],
      MergedOppsAnns[new_internationalcountyname]
    ),
    CALCULATE(SUM(MergedOppsAnns[new_announcement.1.new_jobs])),
    ,
    DESC,
    Dense
)

      Most importantly, you need to double-check the usage of the functions you use to make sure they meet your real needs and get the right sorting results!
      You can check the following links to see more filtering dax functions
      URL:

      Filter functions (DAX) - DAX | Microsoft Learn

      SUMMARIZE function (DAX) - DAX | Microsoft Learn


      I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
      Best Regards,
      Carson Jian,
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.