Calculate a Measure if this Measure is not blank in this and last year

[crisc]

Hello everyone,

after a day of trying stuff on my own i got to admit it: i need help 😄

My problem:

I got a specific measure that slows my report down (takes about 5 minutes to load each time i filter something).

Details:

I got a measure X (some simple COUNTROW and DISTINCTCOUNT - nothing fancy here).

Then i got X_LastYear (CALCULATE(X, SAMEPERIODLASTYEAR(...)) ).

Everything sooth and fast up to this... now comes what starts to make my blood boil:

I want to know the result from X, if X and X_LastYear are both not blank.

I got two version to work and both of them are far to slow use them.

1.

X in both= 
VAR __LEFT=
FILTER(
    ADDCOLUMNS(
        'table';
        "FLAG";[X]
    );
    [FLAG]>0
)

VAR __RIGHT=
FILTER(
    ADDCOLUMNS(
        'table';
        "FLAG";[X_LastYear]
    );
    [FLAG]>0
)

RETURN
COUNTROWS(
    INTERSECT(
        __LEFT;
        __RIGHT
    )
)

Works fine... and gives you the time to solve all the worlds problems inbetweeni its calculations 🙂

2.

X in both= 
VAR __SUMM=
FILTER(
    ADDCOLUMNS(
        SUMMARIZE(
            'table';
            'table'[column]
        );
    "FLAG";[X]+[X_LastYear]
    );
    [FLAG]=2
)

RETURN
CALCULATE(
    [X];
    __SUMM
)

And again: Worlds problems are solved....

Here i got two questions:

1.  Is this the best practice for SUMMARIZE/ADDCOLUMNS? (I doubt it but i can't figure out a better way...)
2. Is there any other way to solve this?

Thanks in advance

Best, Chris

[v-cherch-msft · Accepted solution]

Hi @crisc 

It seems the second measure is perfect.I would suggest you have a look at this article about Best Practices Using SUMMARIZE and ADDCOLUMNS.

Regards,

Cherie

[crisc]

Hi @v-cherch-msft,

---

It seems the second measure is perfect.

I am glad to hear that, even it won't solve my problem 😉

Thanks for the article you mentioned, it helped to understand what these functions really are doing.

For now i will cross this measure from my to-do list... if i can get it to work sometime in a proper way, i will give you an update here.

Best,

Chris