Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @hobe1
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
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
@hobe1 you have to put seperate filter on t2 table, you cannot use related in this case.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @hobe1
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
First thanks for the reference links really useful blog however after several efforts I still cannot use RELATEDTABLE or CALCULATEDTABLE as a subsitute for RELATED in the measure I am trying to create. I am posting some detail below
1. My goal: To create a measure that returns the number of unique rows across two related tables that meet two seperate criteria based on 2 attributes one in either table. The criteria are:
2. Tables and relationships of datasources. The tables at the centre of the issue are linked via ODS Code in a many to 1 relationship. The 'Trust_WiFi ImpProg CRM is a dynamic feed from CRM and the 'Trust WiFi Core Data' table contains static reference content. Usually each trust will only has one ProgressIndex associated with it but occasionally the collection process can result in an extra row for a few trusts, these extra rows are not duplicates and are removed when detected but when this happens my COUNTROWS dashboards measures using RELATE no longer work and as I now understand this is to be expected from the material you provided. So to prevent this break in function I changed the relationship between the two tables tables to many to one as shown below and tried to adjust my measures using RELATEDTABLE OR CALCULATETABLE in place of RELATE
Measures:
The measure that works for the 1:1 relationship but breaks if a trust has more than one progress index associated with it is:
S1 Trusts Live = (Countrows(FILTER('Trusts WiFi Dashboard Core Data',[Rollout Priority]="Stage1 (Priority Trusts)"&&RELATED('Trusts_WiFi ImpProg CRM'[cc_progressindex]) >3)))
The measures I have tried to create that fail after creatting the many to 1 relationship to compensate are:
Hi @hobe1
Could you provide the .pbix file for us to test? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards,
Cherie
Hi @v-cherch-msft ,
Thanks for the reply. Whilst the data is not sensitive it is work related so I would rather not upload the pbix file. I am happy to answer any questions you may have regarding my issue and do any troubleshooting on proposed solutions or corrections. Thanks to the links you sent me I believe RELATE is not suitable for use in Many to 1 related tables which is what I have (see below)
My goal is unchanged (see earlier post) which is to create a measure that returns a count of the number of rows that meet multiple criteria based on the value of Rollout Priority (type text) in one table and cc_progressindex (type whole number) in another related table (see diagram above)
The two criteria as individual measures both work
C1 = Countrows(FILTER('Trusts WiFi Dashboard Core Data',[Rollout Priority]="Stage3 (Wider Rollout)"))
C2 = COUNTROWS(FILTER('Trusts_WiFi ImpProg CRM',[cc_progressindex]>3))
My problem is combining them into a single countrow measure that evaluates both criteria to return a count. I have tried creating measures along the lines of:
Hi @hobe1
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
Apologies for delayed reply I only have one excuse .. Christmas!
So I tried your solution and it worked straight away. I am still learning DAX and BI but I think I will stop trying to code functions in one big linear string in BI and use the structured approach in the code snipett you sent through. So just to check my understanding the CALCULATETABLE function returns the one argument needed by the COUNTROWS function and the CALCULATETABLE has one Table expression and 2 filters within it. I think I was making a real hash of the syntax but thank you so much for persevering with the dialogue, I will mark your solution as the accepted solution and hope you have a great New Year,
All the Best
Howard
Hi there, I follow the example given and the results in the cells are correct, however, the Totals do not add up....
My DAX is below base don the above example...
=COUNTROWS(CALCULATETABLE(data,
FILTER(data,data[Cost]<>MIN(d_Courses[1 course £net]) && data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[2 course £net]) && data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[3 courses £net]) && data[Attend #]>0)
))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |