Refer to field in a measure without using MAX, MIN, SUM, etc

[jimbob2285]

Hi

Can someone help me please - I have the following measure that's not working in all instances, and I think I know why, but I don't know how to fix it

_SelectedMonth_Measure = 
VAR MyFilterTable = SUMMARIZE(FILTER(ALL('Month'), 'Month'[AccountingDate_ME] <= SELECTEDVALUE('Month'[AccountingDate_ME])), 'Month'[AccountingDate_ME])
RETURN
IF(MAX(Transactions[AccountingDate_ME]) IN MyFilterTable, 1, 0)

If you follow the link to the example PBIX file Ledger Transactions, you'll see that when any month other than the latest month is selected, the second table (VOUCHERAREADATAID) has no data

• The first three tables are filtered with the above measure, which is designed to filter out transactions which are greater than the selected month
• the fourth tabel is not filtered by this measure and so shows data regardless
• I think it's becuase the measure use MAX... "MAX(Transactions[AccountingDate_ME]) IN MyFilterTable"
• It has to be a measure, rather than a column, so that it reacts to the slicer selection
• So when ever eny month other than the max month is selected the condition is not true
• But how do I refer to the AccountingDate_ME field in a measure without using an aggregate function (MAX, MIN, SUM, etc.)
• I get that it's to do with context, which is a concept that I'm rapidly getting to undertand
• But in that case, why does it still seem to work OK for the 'Account' field in the third table
• And why does it show 'VOUCHERAREADATAID' in the first table, just because that also shows 'Account'

Please help, it's a constant source of annoyance that I have to aggregate text columns in a measure, just because it's a measure, and I consequently use columns wherever possible

Cheers

Jim

[Bibiano_Geraldo]

Hi @jimbob2285 , i tried to make some changes in your DAX, let me know that the bellow work for you, if no, please provide the desired output:

_SelectedMonth_Measure =
VAR MyFilterTable =
    FILTER(
        ALL('Month'),
        'Month'[AccountingDate_ME] <= SELECTEDVALUE('Month'[AccountingDate_ME])
    )
RETURN
SUMX(
    Transactions,
    IF(
        COUNTROWS(
            FILTER(
                MyFilterTable,
                'Month'[AccountingDate_ME] = Transactions[AccountingDate_ME]
            )
        ) > 0,
        1,
        0
    )
)

[jimbob2285]

Hi

Thanks for your response, I've tried implementing your changes, but they don't work either

I need a measure to use as a filter that produces a 1 or a 0:

• 1 if the month is <= to the selected month in the month slicer
• 0 if the month is > the selected month in the month slicere

the SUMX in your new measure is summing the count of matching rows, as opposed to producing a 1 or 0

if I use your measure without the SUMX it does the same as my original measure, but doesn;t overcome the need for aggregation because it's a measure.

if it didn't need to use the slicer selection then I'd create the filter as a column instead, but it needs to be a measure, so i need to find away to write the measure without aggregating the month in the data, i.e. without the MAX()

Cheers

Jim

[Bibiano_Geraldo]

Is this help you @jimbob2285 ? if no, please consider o share no sensitive file o see closer what you are facing.

_SelectedMonth_Measure =
VAR SelectedMonth = SELECTEDVALUE('Month'[AccountingDate_ME])
RETURN
IF(
    Transactions[AccountingDate_ME] <= SelectedMonth,
    1,
    0
)

[jimbob2285]

Hi

Your second suggestion is essentially a simplified version of my current measure, and works fine as a column but not as a measure, due to the need for aggregation, i.e. MAX(Transactions[AccountingDate_ME])

there's a link to an example file in my original post, but here it is again Ledger Transactions, I've simplified the data set to just 6 records, so it shoudl be easier to see what I'm after

When I seletct the latest month, it works fine, but not when i select any other month, becuase the measue is comparing the max(month), with the selected month due to the filter having to be a measure

Cheers

Jim 

[v-zhengdxu-msft · Accepted solution]

Hi @jimbob2285 

Actually, it's impossible to achieve this.

You can refer to your another thread: Solved: Measure filter not working - Microsoft Fabric Community

You can understand the Table view and Report view as 2 hierarchy, the filter is in the report view and it can filter the data from Table view and cannot affect the data from Table view so that these data are presented in the report with different filtering effects. 

In your first thread, you can get the result by creating a calculated column which can return a 1 or 0(but it's static), because the data from the Table view is unsummarized, you can filter by entry(0 and 1) in the Filter.

But the table visual which you created is summarized so it can not be filtered by entry:

Specifically, the reason is that you can use the calculated column to group by your data(by giving the 1 or 0 as an entry) and using the filter in the next hierarchy to get the data which you want then aggregate them, but this is not applicable to Measure.

In this case, this measure could get the right result by adding more fields in it to make the data non-aggregated, for example [ACCOUNTINGDATE].

Please try to use the FILTER() function in the measure:

MEASURE =
VAR _Slicer =
    SELECTEDVALUE ( 'Month'[AccountingDate_ME] )
RETURN
    CALCULATE (
        SUM ( 'Transactions'[ACCOUNTINGCURRENCYAMOUNT] ),
        FILTER ( 'Transactions', 'Transactions'[AccountingDate_ME] <= _Slicer )
    )

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.