cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 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
Super User V

## 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
Super User V

## 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
Helper I

## 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
Super User V

## Re: Dax Measure

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

Regards,

Harsh Nathani

Highlighted
Helper I

## 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

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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

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

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors