cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Dax Measure

Hi,

I know im being stupid but this is driving me crazy.

Based on the below data i would like to retrieve all lines where one of the lines has "Flag" = 1. I have included what the results should look like below.

Example Data

 ID Flag City Date Amount 1 0 Timbuktu 15/12/2019 499 1 0 Timbuktu 15/12/2019 162 1 0 Timbuktu 15/12/2019 306 2 0 Dubai 15/12/2019 107 2 0 Dubai 15/12/2019 370 2 1 Vatican City 20/12/2019 358 8 0 Dubai 15/12/2019 46 8 0 Dubai 15/12/2019 45 8 0 Dubai 15/12/2019 144 8 0 Dubai 15/12/2019 914 8 1 Dubai 15/12/2019 817 8 0 Dubai 15/12/2019 737

Required Result

 ID Flag City Date Amount 2 0 Dubai 15/12/2019 107 2 0 Dubai 15/12/2019 370 2 1 Vatican City 20/12/2019 358 8 0 Dubai 15/12/2019 46 8 0 Dubai 15/12/2019 45 8 0 Dubai 15/12/2019 144 8 0 Dubai 15/12/2019 914 8 1 Dubai 15/12/2019 817 8 0 Dubai 15/12/2019 737

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III

## Re: Dax Measure

Create a calculated column that will say whether the row is to be shown:

``````ShowLine =
VAR res_ = CALCULATE(SUM(Table1[Flag]);ALLEXCEPT(Table1;Table1[ID]))
RETURN
IF ( res_ > 0; "YES"; "NO")``````

Then place all the fields you want to show in a  Table visual and filter for ShowLine = "YES"

It can also be done with a measure for the filtering, instead of a calculated column

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Highlighted
Community Champion

## Re: Dax Measure

Please share sample data for both the tables and the relationship between them.

Regards,

Harsh Nathani

5 REPLIES 5
Highlighted
Super User III

## Re: Dax Measure

Create a calculated column that will say whether the row is to be shown:

``````ShowLine =
VAR res_ = CALCULATE(SUM(Table1[Flag]);ALLEXCEPT(Table1;Table1[ID]))
RETURN
IF ( res_ > 0; "YES"; "NO")``````

Then place all the fields you want to show in a  Table visual and filter for ShowLine = "YES"

It can also be done with a measure for the filtering, instead of a calculated column

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Highlighted
Community Champion

## Re: Dax Measure

To Display =

var __a = CALCULATE(SUM(Table3[Flag]), ALLEXCEPT(Table3,Table3[ID]))
return
IF (__a >=1 ,"Yes",Blank())

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Highlighted
Frequent Visitor

## Re: Dax Measure

Hi - Massive thanks. The flag number is in another table. I tried using the below as a measure and it has not filtered it correctly. Sorry for not mentioning the second table in the original post

Includes Cancelled =
Var res_=
CALCULATE(SUM(Policy[flag Number]),
ALLEXCEPT(Transactions,Transactions[User ID]))

RETURN
IF(res_>=1,"Y","N")
Highlighted
Community Champion

## Re: Dax Measure

Please share sample data for both the tables and the relationship between them.

Regards,

Harsh Nathani

Highlighted
Frequent Visitor

## Re: Dax Measure

Hi,

I constructed a basic model using the criteria we discussed on two tables and it worked. I cant see why it does not work in my first model but i am unable to share it. Thanks in advance and Kudos coming your way 😉

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors