Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

using RANKX and ALLSELECTED together with visual filters

  • 1.  using RANKX and ALLSELECTED together with visual filters

    Posted Apr 15, 2019 07:05 PM
      |   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)

    Power Summit (bi) - Post


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

    Silver Contributor
    Posted Apr 16, 2019 06:09 AM
    Use ALLEXCEPT instead of ALLSELECTED.

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

    Power Summit (bi) - Post


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

    Bronze Contributor
    Posted Apr 16, 2019 06:46 AM
    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
    ------------------------------

    Power Summit (bi) - Post


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

    Bronze Contributor
    Posted Apr 16, 2019 02:43 PM
    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
    ------------------------------

    Power Summit (bi) - Post


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

    Posted Apr 16, 2019 06:54 PM
    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.
    Power Summit (bi) - Post


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

    Bronze Contributor
    Posted Apr 17, 2019 02:34 AM
    Edited by Andrew Simmans Apr 17, 2019 02:40 AM
      |   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)

    Power Summit (bi) - Post


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

    Posted Apr 17, 2019 06:29 PM
    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.
    Power Summit (bi) - Post