Forum Discussion

dfervwrqqq's avatar
dfervwrqqq
New Member
2 years ago

Measure with two filters (one with function ALL)

Hi,

 

I have measure like this:
```

Measure=
VAR __year =
    FORMAT ( TODAY (), "YYYY" )
VAR numerator =
    CALCULATE (
        DISTINCTCOUNT ( Fact_Table[Order Number] ),
        FILTER(ALL(Calendar[Date (Year)]), Calendar[Date (Year)] = __year),
        FILTER(Fact_Table,Fact_Table[Area] = "condition")
    )
RETURN
    numerator

```

I have some issue. To be more specific, when I switch the year from 2023 to 2022 I only see the value 'Blank()'. But my expected result should be value from 2023 (always, regardless of the selected year). Do you know, how should I change this measure? Thanks in advance.

 

 

Need Help

2 Replies

  • Ced-Alcaraz's avatar
    Ced-Alcaraz
    Regular Visitor
    2 years ago

    dfervwrqqq 
    Create a helper table that contain all the distinct years.

    Year

    2022

    2023

     

    Measure = 
    var _selectedYear = MAX(HELPERTABLE[Year])

     

    RETURN

    CALCULATE (
            DISTINCTCOUNT ( Fact_Table[Order Number] ),
            FILTER(Calendar[Date (Year) = _selectedYear)
        )
     
    *Slicer value should be the Year column of the Helper Table
    *No relationship between main table and helper table
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dfervwrqqq ,

    Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.