Forum Discussion
CountRows measure with multiple filter conditions based on columns in different tables
Hi All,
I am just trying to work out the basic syntax and function rules for creating a measures to count rows that meet 2 different filter conditions based on two columns in different tables. As I am looking for rules I will genericise the examples with that in mind this is what I have found so far:
Example 1: Two unrelated tables (T1 and T2) creating a measure M1 based on column T1a in T1 and T2c in T2
M1 = (Countrows(FILTER('T1',[T1a]= condition1&&'T2'[T2c]=condition2 ))) Works
Example 2: (T1 and T2) related by a 1:1 relationship (crossfilter = both) creating a measure M1 based on column T1a in T1 and T2c in T2
M1 = (Countrows(FILTER(T1,[T1a]=condition1&&RELATED('T2'[T2c]) condition 2))) Works
however and here is my issue
The RELATED function only appears to work with Tables that have a 1:1 relationship setup in Manage Relationships. If the the relationship between T1 and T2 is 1 to many relationship. The Example 2 measure now gives an error
"The column 'T2[T2c]' either doesn't exist or doesn't have a relationship to any table available in the current context.
Can anyone suggest how I can modify Example 2 to work with 1 to many relationships? I would like to avoid havingf to crerate additional calculated tables if at all possible as I feel example 2 should work if the right syntax and appropriate functions are used. I am just not sure where I am going wrong,
many thanks
Hi Anonymous
You may try below measure. Attached the simplified sample file for your reference. Let me know if it matches your request.
Measure = COUNTROWS ( CALCULATETABLE ( 'Trusts_WiFi ImpProg CRM', FILTER ( 'Trusts_WiFi ImpProg CRM', 'Trusts_WiFi ImpProg CRM'[cc_progressindex] > 3 ), FILTER ( 'Trusts WiFi Dashboard Core Data', 'Trusts WiFi Dashboard Core Data'[Rollout Priority] = "Stage3(Wider rollout)" ) ) )Regards,
Cherie
10 Replies
- parry2k
Super User
Anonymous you have to put seperate filter on t2 table, you cannot use related in this case.
- Hi, parry2k Thanks for the reply could you be a little more specific, perhaps provide some example code using the T1/T2 nomenclature I have used to illustrate your proposed solution?
Many thanks
Hobe1
v-cherch-msftMicrosoft Employee
Hi Anonymous
RELATED function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. The M1 will work if the relationship is Many (T1) to 1 (T2). You may refer this article THE MAGIC OF RELATED & RELATEDTABLE FUNCTIONS IN DAX. If you need further help, sample data and expected output will be helpful to provide an accurate solution.
Regards,
Cherie
Hello,
I have a smilar problem but I need to add timestamp as a filter
I have a table like below (four Columns, One is text other three are time stamps)
Name TimeStamp A Time stamp B Time Stamp C
X 2/2/2019
Y 2/2/2019 2/3/2019 2/3/2019
Z 3/10/2019 3/11/2019
I want a matrix where when I filter by A it counts the number of times all three time stamps occure in each columns for example (Table that I'd like to make):
(Filter = Name) Month 2 Month 3 Month 4
Time Stamp A 2 1
Time Stamp B 1 1
Time STamp C 1
