topic Re: 2 measures with 2 distinct years not working in DAX Commands and Tips

2 measures with 2 distinct years not working

Anonymous — Fri, 03 Jan 2020 10:40:29 GMT

Hi folks,

I have a slicer from where I select 2 distinct years, imagine 2018 and 2020 and i want to compare 2 distinct measures, one for the context of 2018 and another one for 2020.

Any one know why isn't this working since there are 2 distinct measures for each context?

Thanks in advance for your help.

Re: 2 measures with 2 distinct years not working

Anonymous — Fri, 03 Jan 2020 11:42:01 GMT

Hi Anonymous

First check your N-X and X are showing correct values i.e 2018 and 2020.

Second remove all from filter section.

If issue still persist then share your dataset. I will check.

Thanks & regards,

Pravin Wattamwar

Re: 2 measures with 2 distinct years not working

Anonymous — Fri, 03 Jan 2020 11:56:10 GMT

Remove that ALL or else you can add KEEPFILTERS Before "Filter".

For More About KEEPFILTERS -https://www.sqlbi.com/articles/using-keepfilters-in-dax/

Thanks & regards,

Pravin Wattamwar

Re: 2 measures with 2 distinct years not working

Anonymous — Fri, 03 Jan 2020 14:32:52 GMT

Thank you for your assistance, but doesn't work neither using keepfilters, without ALL, with KEEPFILTERS and FILTER.

I've tried all the options and perhaps it's a question of dataset.

to simplify I explain my dataset to you, it's a date table and a a fact table joined by DateasInteger key on one to many both sides relation.

The measure is a distintcount of unique references on the fact table

the filter is based on the date table, but i've tried to filter based on the date column of the fact table and behaviour is the same. PBI is summing all rows from both years.

Thanks in advance for your help.

Kind regards,

Re: 2 measures with 2 distinct years not working

az38 — Fri, 03 Jan 2020 14:47:27 GMT

Hi Anonymous

try to replace your PROD_TAB[DATEVAL].[Year] to YEAR(PROD_TAB[DATEVAL])

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: 2 measures with 2 distinct years not working

Anonymous — Fri, 03 Jan 2020 15:22:23 GMT

Thank you for your suggestion but didn't work either.

Re: 2 measures with 2 distinct years not working

az38 — Fri, 03 Jan 2020 15:28:04 GMT

Anonymous

it looks like it should be enough to use

= CALCULATE( DISTINCTCOUNT(PROD_TAB[REF]);YEAR(PROD_TAB[DATEVAL])=[Yr_N-x] )

or (it depends on your data model)

= CALCULATE( DISTINCTCOUNT(PROD_TAB[REF]);YEAR(PROD_TAB[DATEVAL])=YEAR([Yr_N-x]) )

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: 2 measures with 2 distinct years not working

Anonymous — Fri, 03 Jan 2020 16:18:24 GMT

Doesn't work due to this:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Re: 2 measures with 2 distinct years not working

Anonymous — Fri, 03 Jan 2020 18:22:53 GMT

Solved with this suggestion from another Post:

Prior Day Invoiced =
VAR PreviousBusinessDay = [Priorbusinessday]
RETURN
    CALCULATE (
        SUM ( 'Sales Ledger Transactions'[Net Sales] ),
        'Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = "INVOICED",
        'Sales Ledger Transactions'[Date.Date] = PreviousBusinessDay
    )