How to count distinct occurences of one variable for each distinct value of another variable

[aloosh89]

Hello,

New to writing DAX code and I have been stuck with this. I beleive it is simple as I can do it quickly with a for loop but in DAX not sure how to go about it.

Below is a sample table. The last column is not part of the data but an indicator of what gets counted.

I would like to count for each Asset Name the unique occurences of Asset Feature under a filter of Asset Environment  = 'Cold'.

The desired result should be a count of 4.

Asset Name	Asset Feature	Asset Environment	Desired Outcome from counting function
Asset1	x	Cold	Gets counted
Asset1	x	Cold	Does not get counted since value 'x' occurs previously for this asset
Asset1	z	Cold	Gets counted
Asset2	y	Cold	Gets counted
Asset2	x	Cold	Gets counted even though 'x' exists in Asset 1.
Asset3	w	Hot	Does not get counted due to 'Hot' environment

Thanks in advance!

Ali 

[LuizKoller · Accepted solution]

Hello!

Measure = 

VAR TableData = 
SUMMARIZE(
    FILTER(
        'TableName',
        TableName[Asset Environment] = "Cold"
    ),
    'TableName'[Asset Name],
    'TableName'[Asset Feature]
)

RETURN
COUNTROWS(TableData)

[aloosh89]

Hello Again,

The solution works but I found an added layer of complexity when I went to implement on the actual data model I have.

My fact table (let's calll it Fact_Table) has the columns "Asset Feature" and "Asset Environment" but does not have the "Asset Name" column. Fact_Table also has a column "Activity_ID". 

The Fact_Table is linked in the data model to another table (let's call it Activity_Table). Activity_Table has a one to many relationship with FACT_Table with primary key being “Activity_ID”. 

Finally Activity_Table is linked to a third table (let’s call it Asset_Table). Activity_Table has a one to many relationship with Asset_Table with primary key being “Activity_ID” as well. In this Activity_Table is where the “Asset Name” is which is required for the counting as per my initial sample table.

I believe since the intermediate table Activity Table links the Fact_Table with the Asset_Table using that same “Activity_ID”, I should be able to modify the DAX code to achieve the desired result but it probably requires embedding yet another nested summarize or something of that sort.

Would be grateful if there is a solution!

Thanks

Ali

[LuizKoller]

Can you please provide example data?

I tried to replicate the database as explained, but I got confused with the Activity ID

[aloosh89]

Thanks so much!