Interaction between DAX coumn and filters

[AutoJL]

Hi everyone!

I have an issue which I believe is related to my lack of understanding on how custom DAX columns interact with filters.

So my set of data contains lots of IDs and some associated attributes for those IDs ( This is a result of an unpivot operation as you can see in the picture, the original data had one ID per record and an "Attribute" column with a list/string of attributes and I converted it to multiple rows ). 

This is how the data looks like:

The first thing I wanted is to know number of attributes each ID had ( considering now that the table is unpivoted there are many rows with the same ID). For this task this DAX code for the column seems to be doing great:

Number of Attributes per ID = CALCULATE(COUNT([ID]),FILTER(AttributesTable,[ID]=EARLIER([ID])))

After that I wanted to know the average number attributes that each ID had ( also bearing in mind that you want the number of attributes of each ID counted only once per ID instead of just the average of this newly created column that would count all duplicated values since there are lots of duplicated rows). For this a fellow member of the community had previously helped me with a similar measure so I used this:

Average Attributes per ID = AVERAGEX( SUMMARIZE('AttributesTable',AttributesTable[ID],AttributesTable[Number of Attributes per ID]) , AttributesTable[Number of Attributes per ID])

This also seems to work , as if I count the total of Attributes and the total of distinct IDs and proceed with the division it is correct. The issue comes when trying to use other category tables that I have and stablish some filters, in this case the DAX column doesnt change at all which of course results in the average being incorrect.

For instance if I have a slicer and select only "Text1 , 2 and 3" for Attributes, the number of Attributes per ID for ID 0000998877.1 remains at 8 and ,of course, the Average function gives an incorrect result.

I have tried using some ALL and ALL except in the filter for the DAX column but I have been unsuccessful. 

I am aware I could just use a measure count(Attributecode) / distinctcount(ID) and would achieve this result and interact with my slicers but I was wondering if I could manage that result with DAX, I am trying to learn it and want to get familiar with it. 

What am I missing for this column to behave as expect?(Or perhaps I am overcomplicating the data model by trying to use custom DAX column for that?)

Many thanks in advance!

[amitchandak · Accepted solution]

@AutoJL , A calculated column or table does no interact with the slicer/ filter. They are static.

So you might need work with measures

example

Number of Attributes per ID = CALCULATE(COUNT([ID]),FILTER(allselected(AttributesTable),[ID]=max([ID])))

refer if needed Power BI Beginners!
: https://www.youtube.com/watch?v=cN8AO3_vmlY

[AutoJL]

Hi @amitchandak !

Many thanks for the help. That measure indeed did the same thing as the calculated column! I was not able to use it to calculate the average though. I any case I realized that I could do it using simple measures and then making use of the proper relations/filtering for the attributes.

Bookmarked your video and channel!