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

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 =

CALCULATE (

SUM ( ‘Discoverer-Payables’[Net Amount] ),

FILTER ( ‘Discoverer-Payables’, ‘Discoverer-Payables’[Default Effective Date] &&‘Discoverer-Payables’[Concatenate Seg1,Vendor No,Cheque No])

)

VAR APAmount =

CALCULATE (

SUM ( ‘AP Payment 1’[Amount] ),

FILTER (

‘AP Payment 1’ ,

‘AP Payment 1’[Concatenated Seg1,Vendor No,Cheque No]

)

)

VAR Difference_AP1 = ROUND ( DiscovererAmount - APAmount, 0 )

RETURN

IF (

‘Discoverer-Payables’[Difference_AP1] = 0,

“Matched”,

IF (

AND (

OR (

DiscovererAmount = BLANK (),

ROUND ( DiscovererAmount, 0 ) = 0

),

‘Discoverer-Payables’[Difference_AP1] <> 0

),

“Ignore”,

IF (

AND (

APAmount = 0,

ROUND ( DiscovererAmount, 0 ) <> 0

),

“Void/Unmatched”,

“Unmatched”

)

)

)

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.

Thanks

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!