Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

HASONEFILTER for drillthrough

Hi everyone,

 

I am looking how to address the field, which is created "automatically", when you drill down in a bar chart. 

For example: I have a date hierarchy (Year-Month) and I drill down to a specific year and get all 12 months. See here:

Hierarchy Filter

 

Now my goal is to use this filter in HASONEFILTER. Is that somehow possible?

 

What I want to achieve finally is, that when I display the years of my hierarchy, I only want to sum the month = 12 of the facts. When I drill down to month, I want to display all the months.

 

Any help is appreachiated.

 

Thank you in advance,

best

J

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi everyone,

     

    I have found a solution to my problem.

    If you always want to have the latest month of a year, you could use this formula:
    msr_YourName = CALCULATE(SUM(tableName; LASTDATE(dateColumn))

     

    Then, if the date hierarchy (use a well designed date table) is created with only year and months, the legend is selected and the msr_YourName is used as the values, it gave me exactly what I was looking for. 

    To everyone involved, thank you very much for your help!

    Best

    J

3 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    The title is about drillthrough. And the description is about drill down. If you want to implement it while drilling down, you could try the function of ISFILTERED and drag the columns of year and month into the axis filed rather than a date hierarchy. I create a sample  that you could try and check if it is what you want.

     

    Measure =
VAR a =
    CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[MONTH] = 12 ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[YEAR] ),
        a,
        IF ( ISFILTERED ( 'Table'[MONTH] ), SUM ( 'Table'[Sales] ) )
    )

    Best Regards,

    Xue Ding

    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
      6 years ago

      HI v-xuding-msft,

       

      thank you very much for your reply!

       

      This is almost what I want and your example works as I would expect it. But, if I have multiple years, I have a different output to what I would expect:

      When one clicks on the double arrow (down), one comes down a hierarchy level. So far so good, but I have multiple different years, I don't want to see the sum (monthly aggregate) of all years but only the year I was clicking on.

       

      From what I know, to drill down on one specific Year the single arrow (down) is to use. But when I do this, I only see the month 12 but not the other months.

       

       

      One additional thing: How could I accomplish such a thing, when the last month of the latest year not december is? (The report is on a monthly basis and each month, there is a new month. Thus, I would like to display 2019-12, but 2020-01). Is it possible to do so? (As far as I understood the code, when month 12 is not available, the whole year will not be displayed).

      Thank you in advance,

      Best regards
      J

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi everyone,

         

        I have found a solution to my problem.

        If you always want to have the latest month of a year, you could use this formula:
        msr_YourName = CALCULATE(SUM(tableName; LASTDATE(dateColumn))

         

        Then, if the date hierarchy (use a well designed date table) is created with only year and months, the legend is selected and the msr_YourName is used as the values, it gave me exactly what I was looking for. 

        To everyone involved, thank you very much for your help!

        Best

        J