Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hobe1
Helper II
Helper II

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

1 ACCEPTED 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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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

parry2k
Super User
Super User

@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, @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

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-cherch-msft

 

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:

 

  • 'Trust_WiFi ImpProg CRM'[cc_progressindex] >3
  • 'Trust WiFi Dashboard Core Data'[Rollout Priority]="Stage3 (Wider Rollout"

 

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

 

Table Relationships.GIF

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

Attempt1: S1 Trusts Live = (COUNTROWS(FILTER('Trusts WiFi Dashboard Core Data',[Rollout Priority]="Stage1 (Priority Trusts)"&&CALCULATETABLE(DISTINCT('Trusts_WiFi ImpProg CRM'),'Trusts_WiFi ImpProg CRM'[cc_progressindex]>3))))
 
Syntax is fine but it fails with a logic error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
 
Which I dont understand because the whole point is to do a countrows across multiple columns. I also think I have the DISTINCT function in the wrong position but can sort that out later
 
Anything you can provide to help me understand where I am going wrong with this would be greatly appreciated
 
 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)

 

Table Relationships map.GIF

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

M1 = (Countrows(FILTER('Trusts_WiFi ImpProg CRM',([cc_progressindex])>3))&&(RELATEDTABLE('Trusts WiFi Dashboard Core Data'),[Rollout Priority]>3))
 
Which gives an error: Operator or expression '( )' is not supported in this context. Whatever that means
 
and
 
M1 = Countrows(FILTER('Trusts WiFi Dashboard Core Data',[Rollout Priority]="Stage3 (Wider Rollout)"))&&FILTER('Trusts_WiFi ImpProg CRM',[cc_progressindex]>3)
 
Which gives an error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
 
Any ideas on taking this forward?
 
Many Thanks 
 
Howard
 
Howard

 

 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-cherch-msft

 

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)

))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.