DAX to Filter on the same field with different conditions

[Sabarikumar7579]

This is a sample data table. My requirement is to calculate:
How may People who are from Marketing in 2018 were part of 2017 Sales? Answer is : 4 (C,D,E & F)

I am not sure if this has a simple trick to calculate in dax  but I am not able to answer .

Name	Year	Program
A	2017	Sales
B	2017	Sales
C	2017	Sales
D	2017	Sales
E	2017	Sales
F	2017	Sales
G	2017	Sales
A	2017	Marketing
B	2017	Marketing
C	2017	Marketing
H	2018	Sales
I	2018	Sales
J	2018	Sales
K	2018	Sales
L	2018	Sales
C	2018	Marketing
D	2018	Marketing
E	2018	Marketing
F	2018	Marketing

[Anonymous · Accepted solution]

@Sabarikumar7579,
if you are looking to count the number of rows, create a measure like this:

Count of Name = 
VAR t1 = NATURALINNERJOIN (
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2018 && Table1[Program] = "Marketing" ),
        "Name", [Name]
    ),
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2017 && Table1[Program] = "Sales" ),
        "Name", [Name]
    )
)
RETURN COUNTROWS(t1)

Otherwise, if you are looking for a list, use this instead:

List of Name = 
VAR t1 = NATURALINNERJOIN (
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2018 && Table1[Program] = "Marketing" ),
        "Name", [Name]
    ),
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2017 && Table1[Program] = "Sales" ),
        "Name", [Name]
    )
)
RETURN CONCATENATEX(t1, [Name], ", ")

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Sabarikumar7579]

Thank you So much

@Ashish_Mathur and @Anonymous

Both your functions worked. Learnt something new.

[Ashish_Mathur]

You are welcome.

[Anonymous · Accepted solution]

@Sabarikumar7579,
if you are looking to count the number of rows, create a measure like this:

Count of Name = 
VAR t1 = NATURALINNERJOIN (
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2018 && Table1[Program] = "Marketing" ),
        "Name", [Name]
    ),
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2017 && Table1[Program] = "Sales" ),
        "Name", [Name]
    )
)
RETURN COUNTROWS(t1)

Otherwise, if you are looking for a list, use this instead:

List of Name = 
VAR t1 = NATURALINNERJOIN (
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2018 && Table1[Program] = "Marketing" ),
        "Name", [Name]
    ),
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Year] = 2017 && Table1[Program] = "Sales" ),
        "Name", [Name]
    )
)
RETURN CONCATENATEX(t1, [Name], ", ")

[Greg_Deckler]

Hmm, that's some pretty ugly data. I think to do this correctly, you would need something to determine the last status of 2017.