Power BI Exchange

Expand all | Collapse all

using RANKX and ALLSELECTED together with visual filters

  • 1.  using RANKX and ALLSELECTED together with visual filters

    Posted 6 days ago
      |   view attached
    Hi

    I have a matrix, with two levels of aggregation and also has a RANKX measure

    RANKX(ALLSELECTED(ETOs[Job Number]), [Total ETOs To Date])

    It is using ALLSELECTED because in my table, i have another column which I want to filter out via the "visual filters" option on the table. This works fine but i also want to limit the table to show only the 10 ranks (where the rank <= 10). But as soon as I select to also visually filter the rank measure to less than 10, the table is empty, which I understand happens as ALLSELECTED becomes affected but its also being used to calculate the rank.

    But knowing this issue, I have not found a way to have this combination work, can someone please help?

    Also, I tried using the top N visual filter, but this does not work on all levels of the aggegation (for EG, it will only ever show the top 10 rows, even when i expand the categories. I want to have the top 10 show PER level of aggregation.

    Attachment(s)



  • 2.  RE: using RANKX and ALLSELECTED together with visual filters

    Silver Contributor
    Posted 5 days ago
    Use ALLEXCEPT instead of ALLSELECTED.

    ------------------------------
    Lutz
    ------------------------------



  • 3.  RE: using RANKX and ALLSELECTED together with visual filters

    Posted 5 days ago
    Hi James,

    You need to rewrite the calculated measure Job Last Stage into a calculated column (will become static then) and then use this calculated column as a filter into your formula.


    ------------------------------
    Benoit Fedit
    Business Intelligence
    ------------------------------



  • 4.  RE: using RANKX and ALLSELECTED together with visual filters

    Posted 5 days ago
    Hi James

    The problem is that Job Last Stage is a Measure - not a dimension - thus the ranking (or the top 10) is not doing what you expect.

    I think your fundamental structure is wrong - you presumably want to determine different "Job Last Stage" for different selected dates and then to be able to calculate the "To Date" figures against it.

    In my experience the easiest way to do this (but seems weird) is to create a record for each Job and Month - then on that record you can put the "Last Stage". Then duplicate your ETO table so there is a set of records for each period (but only include ones dated before the period). Although this creates a large number of records the space they take up is not bad as the number of unique entries per column drives the volume. Also allows you to get rid of the many to many link.

    Then when you select you month you can select which "set" of Job / Month you are analysing and thus the set of ETOs. You may have to create a composite key to uniquely identify each row in the link perhaps something like the job number plus the month date.

    Hope that helps

    ------------------------------
    Andrew Simmans
    ------------------------------



  • 5.  RE: using RANKX and ALLSELECTED together with visual filters

    Posted 5 days ago
    I see. I never thought about doing it that way. Is this possible using power query to generate the new rows? I want to keep the original query intact to preserve the logic of how the data is being retrieved.


  • 6.  RE: using RANKX and ALLSELECTED together with visual filters

    Posted 5 days ago
    Edited by Andrew Simmans 5 days ago
      |   view attached
    You can in theory do this in Power Query, but I prefer to do it in DAX as I get concerned as to whether Power Query can handle the volumes generated. In your particular model, I think the principle of how to do this can be shown in your model - but it needs further work to create a set per month perhaps using Power Query to create a table which is Job plus month. Anyhow in your model you could create a set per Job Events as follows:

    Add two columns to Job Events:
    Job Event Key = 'Job Events'[PROJID] & "_" & 'Job Events'[ActivitySort]
    EffectiveTo = LOOKUPVALUE('Job Events'[CONSEVENTACTUALENDDATE],'Job Events'[ActivitySort],'Job Events'[ActivitySort] + 1,'Job Events'[PROJID],'Job Events'[PROJID]) (I am guessing that your Activity Sort is ordering into date sequence)
    Then add a table:
    Joined Table = FILTER(CROSSJOIN('Job Events',ETOs),[PROJID]=[JobNo] && ([PostingDate]<=[EffectiveTo] || ISBLANK([EffectiveTo])))
    Then add relationship using the Job Event Key

    You can reduce the size of the Joined Table by selecting the columns you want in the cross join - i.e. crossjoin(selectcolumns( …. or even use summarizecolumns to reduce granularity of the ETOs - i.e. crossjoin(selectcolumns('Job Events …., summarizecolumns(ETOs ...

    I have adjusted your sample

    Hope that helps

    ------------------------------
    Andrew Simmans
    ------------------------------

    Attachment(s)



  • 7.  RE: using RANKX and ALLSELECTED together with visual filters

    Posted 4 days ago
    This is amazing. I had just assumed that it would have to be done in sql or M. I can see a lot of places where I can apply a similar technique. Thanks for the eye opener.