Forum Discussion

jimbob2285's avatar
jimbob2285
Advocate IV
1 year ago
Solved

Measure filter not working

Hi

 

I need some help, please...

 

I've created the following measure filter, which allows the user to select a month and filter a table to show only the months from the beginning of the year up to the month selected, as they have a need to look at the monthly transaction values only up to a certain period

_SelectedMonth_Cumulative = 
VAR MyFilterTable = SUMMARIZE(FILTER(ALL('Calendar'), 'Calendar'[Date] <= SELECTEDVALUE('Calendar'[Date])),'Calendar'[Date])
RETURN
IF(MAX(Ledger[Date]) IN MyFilterTable, 1, 0)

 

The Ledger-Transactions file is a much simplified version of the full data set, but the principles are the same

 

If you open the link to the Ledger-Transactions PBIX file, you'll see that I've created two other measure filters in the same manner, to filter to:

  • Cummulative transactions up to the month previous to the selected month
  • transactions just for the month selected

and while the the measure filter in the code example above is working in the monthly table, It and the other two are not working in the three cards on the left of the page, I can't access the dropdowns to set the filters, it's as if they're invaid

 

Now, I understand why this is, it's a matter of context, which i take to mean that measures only work on the visuals and are not a column in the table, so if the visual doesn't contain the relevant column(s), then the measure won't work as a filter... which makes sense to me

 

And you'll see that for the purposes of demonstrating how I'd like these three cards to appear, I've filtered them with a column filter instead, where the selected date is hard coded into the DAX for the columns

 

But i need to filter them with a meausre, as it needs to be reactive to the month slicer, which a column is not, so the question is, can this be acheived and if so, how?

 

Thanks in advance for any help and advice you can offer

 

Cheers

Jim

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi jimbob2285 

     

    Please try this:

    Here I create 3 measures:

     

    Selected Month - Month =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && MONTH ( 'Ledger'[Date] ) = MONTH ( _Slicer )
        )
    )

    Selected Month - Cumulative =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && 'Ledger'[Date] <= _Slicer
        )
    )
    Previous Month - Cumulative =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && 'Ledger'[Date] < _Slicer
        )
    )

     

    Then add the 3 measures in the three cards, the result is as follow:

     

     

    Best Regards

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

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi jimbob2285 -calculates the sum of transactions from the start of the year up to the selected month dynamically

    Cumulative_UpTo_SelectedMonth =
    VAR MaxDate = MAX('Calendar'[Date]) -- Max date in the current visual context
    RETURN
    CALCULATE(
    SUM(Ledger[TransactionValue]),
    FILTER(
    ALL('Calendar'),
    'Calendar'[Date] <= MaxDate
    )
    )

     

    calculates the sum of transactions up to the month previously month-1 , calculate it by taking the reference of above logic .

    similarly, for we can calculates the sum of transactions for only the selected month.

    SelectedMonth_Transactions =
    VAR MaxDate = MAX('Calendar'[Date])
    RETURN
    CALCULATE(
    SUM(Ledger[TransactionValue]),
    FILTER(
    ALL('Calendar'),
    YEAR('Calendar'[Date]) = YEAR(MaxDate) &&
    MONTH('Calendar'[Date]) = MONTH(MaxDate)
    )
    )

     

    Add a Month slicer from the Calendar table to your report page. I hope this works.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi jimbob2285 

     

    Please try this:

    Here I create 3 measures:

     

    Selected Month - Month =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && MONTH ( 'Ledger'[Date] ) = MONTH ( _Slicer )
        )
    )

    Selected Month - Cumulative =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && 'Ledger'[Date] <= _Slicer
        )
    )
    Previous Month - Cumulative =
VAR _Slicer =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Ledger[Revenue] ),
        FILTER (
            ALLSELECTED ( Ledger ),
            YEAR ( 'Ledger'[Date] ) = YEAR ( _Slicer )
                && 'Ledger'[Date] < _Slicer
        )
    )

     

    Then add the 3 measures in the three cards, the result is as follow:

     

     

    Best Regards

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