cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emudria Member
Member

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

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!

Super User
Super User

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!

8 REPLIES 8
Super User
Super User

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!

emudria Member
Member

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.

Super User
Super User

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!

emudria Member
Member

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

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!

emudria Member
Member

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.

Super User
Super User

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
emudria Member
Member

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 ThanksSmiley Happy