cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nphadro Regular Visitor
Regular Visitor

DAX Formula Explanation

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,

1 ACCEPTED SOLUTION

Accepted Solutions
marcussyliu Frequent Visitor
Frequent Visitor

Re: DAX Formula Explanation

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.

 

View solution in original post

3 REPLIES 3
Seward12533 New Contributor
New Contributor

Re: DAX Formula Explanation

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 Regular Visitor
Regular Visitor

Re: DAX Formula Explanation

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 1.JPG

TABLE 2
table 2.JPG

 

 

 

 

 

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 Frequent Visitor
Frequent Visitor

Re: DAX Formula Explanation

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.

 

View solution in original post

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,386)