DAX Formula Explanation

[nphadro]

I saw a video that showed the formula below:

margin group 2 =
CALCULATE([sales],
FILTER(VALUES(Table1[Profit]),
COUNTROWS(
FILTER(Table2,
Table1[Profit]>=Table2[Min]
&& Table1[Profit]<=Table2[Max]))
>0))

I'm confused on what the "Countrows" does in this context. I get that countrows... counts the number of rows in a given table. But I don't understand why you need it in this formula. When I remove the count rows. it says... "the expression refers to multiple columns. Could someone explain to me what this DAX Expression is doing?

Thanks,

[Seward12533]

As the error message states you can't specify multiple columns.  Not sure about the technical limiations as to why the DAX engine can't handle it but the COUNTROWS > 0 works aroud this as a boolean check to see if any values in TABLE1 are within the range specified in table 2.  Inside the 2nd Filter the scope  (filter context) is already limited by the first filter so your really not specifing the entire table but only a specific value from it. 

Pretty cool trick, this DAX basically eliminates the need to add a CALCULATED column in Table 1 to check to see if the Profit in Table 1 is within a range specified in anothe table. Which is the way I've typically handled it. Don't know what approach is better from a performance point of view but I like it.   

[nphadro]

Hey @Seward12533

Where am I going wrong in my thinking of the logic... I'm still not fully understanding. When I read the formula here is the way I process it (which is obviously wrong). Here are my two tables:

THANKS SO MUCH FOR YOUR HELP

TABLE 1

TABLE 2

My incorrect thought process: (please help me understand where I'm not right).

1) VALUES creates a 1 column table of the "profit" column in table 1

2) the first FILTER runs and iterators over each row (value) in the newly created table in step 1

              In this case it would first loop through "0.2" which is the first row

3) for each row it then looks at the next filter and says find which rows apply

             In this case the only true row is "MED" the sendcond row.

4) Countrows then returns a number or rows that applies which is always 1 so therefore it is greater than 0

5)NOW I'm stuck.... How does it know to only get the "Group column" from the second filter when the second filter is really grabbing the table. You made this comment

""Inside the 2nd Filter the scope  (filter context) is already limited by the first filter so your really not specifing the entire table but only a specific value from it. "" but I'm not fully understanding. 

[marcussyliu · Accepted solution]

Hi,

This is a typical pattern used for dynamic segragation calculation. Note that Table1 and Table2 have no relationship, and you can see Table1 as a fact table and Table2 a parameter table. So the only usage of the measure that makes sense is in a query for sales by group. For example, if you create a matrix visual and drag Group column from Table2 to Row section and the measure to Value section, you will then see how the measure works to return the correct result for each group.

Hope this helps.