Latest Enterprise DNA Initiatives

How to use measure in filter expression

How to filter a table with calculated measure,

For eg: table a filtered by the measure=“abc”

Filter(table a, measure=“abc”)

Its returning incorrect results

Hi Anu,

Please read the post below, can you provide more context and detail so we have a better understanding of what you are working on and what you hope to achieve?

Thank you

I have two tables AP Payment 1 and Discoverer Payables.

In Discoverer Payables- (1)
I need to Group by Default Effective Date,[Concatenate Seg1,Vendor No,Cheque No] take the Sum of '[Net Amount]

In AP Payment 1 - (2)
I need to Group by [Concatenate Seg1,Vendor No,Cheque No] take the Sum of '[Amount]

Then Find the Difference between both (1) and (2), and create a flag

The flag formula as below: (correct me if wrong)

Flag_AP1 =

VAR DiscovererAmount =
SUM ( ‘Discoverer-Payables’[Net Amount] ),
FILTER ( ‘Discoverer-Payables’, ‘Discoverer-Payables’[Default Effective Date] &&‘Discoverer-Payables’[Concatenate Seg1,Vendor No,Cheque No])

VAR APAmount =
SUM ( ‘AP Payment 1’[Amount] ),
‘AP Payment 1’ ,
‘AP Payment 1’[Concatenated Seg1,Vendor No,Cheque No]
VAR Difference_AP1 = ROUND ( DiscovererAmount - APAmount, 0 )

IF (
‘Discoverer-Payables’[Difference_AP1] = 0,
IF (
OR (
DiscovererAmount = BLANK (),
ROUND ( DiscovererAmount, 0 ) = 0
‘Discoverer-Payables’[Difference_AP1] <> 0
IF (
APAmount = 0,
ROUND ( DiscovererAmount, 0 ) <> 0

I am creating 4 flag values : Matched,Ignore,Void/Unmatched,Unmatched.

Now I need to find all the records in AP Payment 1 which has flag = “Matched”

I am trying to use FILTER(AP Payment 1, Flag_AP1 =“Matched”

But there is some problem when using measure as condition in filter.

Hi Anu,

Please provide a sample PBIX with some dummy data
and also show how you want to use or visualize these “Matched” records - thanks

I am doing a data consolidation basically, Corresponding to the “matched” lines of AP Payment 1 , i need to lookup some values from another table. I dont need visualization

I have attached sample pbix where im doing the above said calculations using calculted table.
But if possible let me know how to use measures to do the same

Hi @Anu

Based on your description and Sample file, it seems you are looking to filter the consolidated table itself which is not possible using a Measures. For this you need to create a calculated column only.

If you are looking to filter any visualisation then we can make use of a measure.

Ankit Jain

Thanks for clarifying.

Hi @Anu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!