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

[Anonymous]

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 

Dates	Status	Project
1/01/2018	Completed	Project B
2/01/2018	under progress	Project C
3/01/2018	Started	Project D
3/01/2018	under progress	Project D
3/01/2018	Completed	Project A
3/01/2018	Completed	Project C
3/01/2018	Completed	Project D

As its output I need following output

Dates	Status	Project
3/01/2018	Started	Project D
3/01/2018	under progress	Project D
3/01/2018	Completed	Project 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. 

[Phil_Seamark · Accepted solution]

Hi @Anonymous

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"
        )

[Phil_Seamark]

Hi @Anonymous

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]))

[Anonymous]

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

DataCol1	DataCol2	MyCalculatedColumn	Logic
A	X	YES	If for an item in [DataCol1], [DataCol2] has a value'myValue' in any cell , [MyCalculatedColumn] fills 'YES' in all rows for A, otherwise fill 'NO'
A	Y	YES
A	Z	YES
B	X	YES
B	myValue	YES
C	X	NO
C	Y	NO
D	X	NO
A	myValue	YES
B	X	YES
A	X	YES

Thanks.

[Phil_Seamark · Accepted solution]

Hi @Anonymous

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"
        )

[Anonymous]

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.

Date	DataCol1	DataCol2	MyCalculatedColumn
day1	A	X	YES
day2	A	Y	YES
day3	A	Z	YES
day1	B	X	YES
day2	B	myValue	YES
day1	C	X	NO
dat2	C	Y	NO
day3	D	X	NO
day4	A	myValue	YES
day3	B	X	YES
day5	A	X	YES

[Phil_Seamark · Accepted solution]

Hi @Anonymous

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"
        )

[Anonymous]

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.

[Phil_Seamark]

Hi @Anonymous

Thats right.  

[Anonymous]

@Phil_SeamarkI will sleep in peace tonight  Thanks:)