cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

DAX Query to select all values from a tag, if a particular element is present

Hi All,

 

I need to find a DAX solution to my following problem.

I have a table like this, and I want to select all projects, including all the 'status' states, if the project has started after a date, say 1st of Jan.

so in the below example, I got statuses for Project A..D 

DatesStatusProject 
1/01/2018CompletedProject B
2/01/2018under progressProject C
3/01/2018StartedProject D
3/01/2018under progressProject D
3/01/2018CompletedProject A
3/01/2018CompletedProject C
3/01/2018CompletedProject D

As its output I need following output

DatesStatusProject 
3/01/2018StartedProject D
3/01/2018under progressProject D
3/01/2018CompletedProject D

ie all states of project a that are present, but only if it has started after Jan 01, hence project D.

 

The end purpose is to later filter my data based upon this column, from a huge set of data to only track progress of relevent projetcts,which were started & then progressed during the selected dates.

 

P.S. I asked similar question last week, but didnt realize I need solution in DAX only, till @Ashish_Mathur generously helped me. 

Somehow, couldnt reply back on the same post as it was already accepted as solution.

 

 

Any directions will be highly appriciated.

Thanks. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: DAX Query to select all values from a tag, if a particular element is present

Hi @emudria

 

Please try this calculated column

 

Column = 
    IF (
        CALCULATE(
            COUNTROWS('Table1'),
            FILTER(
                'Table1',
                'Table1'[DataCol1] = EARLIER('Table1'[DataCol1]) 
                && 'Table1'[DataCol2] = "myValue")
                )>0,
        -- THEN --
        "Yes",
        -- ELSE --
        "No"
        )



To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Microsoft
Microsoft

Re: DAX Query to select all values from a tag, if a particular element is present

Hi @emudria

 

Values in calculated columns are locked in stone once evaluated so cannot be changed by filters.

 

Try it as a calculated measure instead.

 

My Measure = 
    IF (
        CALCULATE(
            COUNTROWS('Table1'),
            FILTER(
                ALLSELECTED('Table1'),
                'Table1'[DataCol1] = MAX('Table1'[DataCol1]) 
                && 'Table1'[DataCol2] = "myValue")
                )>0,
        -- THEN --
        "Yes",
        -- ELSE --
        "No"
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Re: DAX Query to select all values from a tag, if a particular element is present

Hi @emudria

 

This calculated table might be close

 

Table = 
VAR Projects = DISTINCT(
                SELECTCOLUMNS(
                    FILTER(
                        'Table1',
                        'Table1'[Status]="Started" && 'Table1'[Dates] >= DATE(2018,1,1)
                        ),"Projects",[Project]))

RETURN GENERATE(Projects,FILTER('Table1','Table1'[Project]=[Projects]))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Helper V
Helper V

Re: DAX Query to select all values from a tag, if a particular element is present

Thanks @Phil_Seamark, Apologies if it feels I am being cheeky, but can we somehow achieve following, in dax. 

DataCol1DataCol2MyCalculatedColumnLogic
AXYESIf for an item in [DataCol1], [DataCol2] has a value'myValue' in any cell , [MyCalculatedColumn] fills 'YES' in all rows for A, otherwise fill 'NO'
AYYES
AZYES
BXYES
BmyValueYES
CXNO
CYNO
XNO
AmyValueYES
BXYES
AXYES

 

Thanks.

Highlighted
Microsoft
Microsoft

Re: DAX Query to select all values from a tag, if a particular element is present

Hi @emudria

 

Please try this calculated column

 

Column = 
    IF (
        CALCULATE(
            COUNTROWS('Table1'),
            FILTER(
                'Table1',
                'Table1'[DataCol1] = EARLIER('Table1'[DataCol1]) 
                && 'Table1'[DataCol2] = "myValue")
                )>0,
        -- THEN --
        "Yes",
        -- ELSE --
        "No"
        )



To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Helper V
Helper V

Re: DAX Query to select all values from a tag, if a particular element is present

Thank you @Phil_Seamark, It is working great except for one thing.

It somehow remembers all the data present in the datamodel.

("YES" filter applied)If I am putting a date slicer, and move the slicer, it still calculates "YES" for the  [DataCol1] items which were "YES" without the date filter.

eg

here is the original table, with original calculations. and I am getting exaclty same results as expected.

but when I put a filter on date from day 3 and onwards... it should show only "YES" for value A, but still shows for A & B.

DateDataCol1DataCol2MyCalculatedColumn
day1AXYES
day2AYYES
day3AZYES
day1BXYES
day2BmyValueYES
day1CXNO
dat2CYNO
day3XNO
day4AmyValueYES
day3BXYES
day5AXYES
Highlighted
Microsoft
Microsoft

Re: DAX Query to select all values from a tag, if a particular element is present

Hi @emudria

 

Values in calculated columns are locked in stone once evaluated so cannot be changed by filters.

 

Try it as a calculated measure instead.

 

My Measure = 
    IF (
        CALCULATE(
            COUNTROWS('Table1'),
            FILTER(
                ALLSELECTED('Table1'),
                'Table1'[DataCol1] = MAX('Table1'[DataCol1]) 
                && 'Table1'[DataCol2] = "myValue")
                )>0,
        -- THEN --
        "Yes",
        -- ELSE --
        "No"
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Helper V
Helper V

Re: DAX Query to select all values from a tag, if a particular element is present

Thanks @Phil_Seamark for your time and helping me out. 

So this mean, practically, within DAX, I wont be able to put such "calculation" in a page level filter. I can have it in a measure, so show the status "YES/NO" in a table or I can have it in a column & hence filter , but only for the data already present in the model.

Highlighted
Microsoft
Microsoft

Re: DAX Query to select all values from a tag, if a particular element is present

Hi @emudria

 

Thats right.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Helper V
Helper V

Re: DAX Query to select all values from a tag, if a particular element is present

@Phil_SeamarkI will sleep in peace tonight Smiley Very Happy Thanks:)

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors