locked
Show figures only on a certain level RRS feed

  • Question

  • I have a figure that I would like to display only when the filter context is cost center, month or year. If the filter context is different than that - for instance if someone drags in the date also then the result should be blank. I can do by the following:

    Valid_figure =

    IF(

    Not(

    Isfiltered(Date[Date]));

    Sum(Costs[Amount])

    )

    That works fine, but my issue is, that I have a lot of other fields that could be draged in the report, so my "NOT" part will be extensive. Is there some way to do it the opposite, so the Amount is only showed if the level is exactly month, year or cost center?

    Tuesday, September 8, 2015 1:49 PM

Answers

  • You could compare the current number of rows in your fact table with the number of rows when all filters except the ones on month, year and cost center are removed:

    IF(

    COUNTROWS(Costs) =

    CALCULATE(COUNTROWS(Costs);

    ALLEXCEPT(Date;Date[Month];Date[Year]);

    ALLEXCEPT(<table where cost center is in>;[Cost Center]);

    ALL(<other table>)

    ); SUM(Costs[Amount]))

    You would still have every table in your formula, but not every column.

    • Proposed as answer by Charlie Liao Tuesday, September 22, 2015 8:55 AM
    • Marked as answer by Morten_DK Tuesday, September 22, 2015 10:42 AM
    Tuesday, September 15, 2015 3:27 PM
    Answerer