So the CALCULATETABLE creates a separate table, but it can be used inside a SUMX to calculate a sum of Spain orders. The caveat, is that you have to use SUMX instead for it to work. Am I understanding correctly? Its a curious approach.
Hi Thank you for such a great insight. I work in training department and our learning modules have 800 learner now they score different marks in different module(course, assessment,etc.) So if I want to find out how many of the learner are above 80% in a specific segment like assessment or course . What dax could be used. I have their marks , total percentage achieved (combined all segments). If you can suggest what dax measure can be used. Thanks. 👍
Thanks for your humble comments. Please check this link: community.powerbi.com/t5/Desktop/Dax-to-get-80-contributed-products-as-a-single-value-on-card/m-p/780281?lightbox-message-images-780281=189000i9F2D75BFBD9148AD
explanation is good, i would like to know why filter function is used inside calculate table function as they both caluclate table and filter return the table.
Thanks for your feedback. CALCULATE Function is a filter modifier that evaluates an expression in a modified filter context. I can give you a long answer here but this won't work. May I ask you to read the definition of CALCULATE and FILTER Function once again? Probably this would solve your query. I hope you won't mind reading it, brother. Always happy to help.
Yes, you can. For example, see the following examples Step 1: Sum of sales for the year 2021 = CALCULATE( SUM(Sales[Amount]), Sales[Year] = 2021 ) Step 2: Now, you can futher filter the sales by specific regions as follows: Total Sales for West Region = CALCULATE( SUM(Sales[Amount]), Sales[Year] = 2021, CALCULATETABLE( FILTER(Sales, Sales[Region] = "West") ) )
Nice explanation. But I think you should also discuss the use cases of each DAX function. For eg, so far to my knowledge, CALCULATETABLE is usually used inside the Iterating functions (such as SUMX, AVERAGEX, etc). Correct me if I'm wrong :) And I can see that both equations give the same result (one using CALCULATE and another using SUMX, CACULATETABLE). Can you advise which one has better performance?? Thanks.
Hi Merida, Yes, I agree, I should have mentioned some use cases as well which I am going to discuss next time. However, I have mentioned that "CalcfulateTable" DAX can be used either as an intermediate DAX function or to create a table.
Please give us the clear picture of many to many relation n why we need to avoid and same explain cross filtering,what is cardinality i know this topics are basics but after watching reading many articles still little bit confused so try to post with few real time senarios
1) When filter expressions are provided, the CALCULATE function modifies the filter context to evaluate the expression. For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function: 2) If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression. 3) If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression. The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It's required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context. This scenario can happen in a calculated column formula or when an expression in an iterator function is evaluated. Note that when a model measure is used in row context, context transition is automatic. 4) This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Really good. Sir teach us banding,aging, disconnected tables topics in upcoming video according to your feasibility.
You are doing gr8.
Thanks
Sure! I'll post soon.. stay tuned
Really awesome. Very easy to understand.
Glad you think so!
it was a very clear explanation, thanks u so much.
Glad it was helpful!
Thanks bro
Thanks!
Welcome! Glad to help.
Please explain filter and row context..
So the CALCULATETABLE creates a separate table, but it can be used inside a SUMX to calculate a sum of Spain orders. The caveat, is that you have to use SUMX instead for it to work. Am I understanding correctly? Its a curious approach.
hi u have wrote complete dax for sumx again y dont we use directly calculated table dat we created already instead of writing calculatetable DAX fn.
Hi Thank you for such a great insight. I work in training department and our learning modules have 800 learner now they score different marks in different module(course, assessment,etc.) So if I want to find out how many of the learner are above 80% in a specific segment like assessment or course . What dax could be used. I have their marks , total percentage achieved (combined all segments). If you can suggest what dax measure can be used. Thanks. 👍
Thanks for your humble comments. Please check this link: community.powerbi.com/t5/Desktop/Dax-to-get-80-contributed-products-as-a-single-value-on-card/m-p/780281?lightbox-message-images-780281=189000i9F2D75BFBD9148AD
explanation is good,
i would like to know why filter function is used inside calculate table function as they both caluclate table and filter return the table.
Thanks for your feedback. CALCULATE Function is a filter modifier that evaluates an expression in a modified filter context.
I can give you a long answer here but this won't work. May I ask you to read the definition of CALCULATE and FILTER Function once again? Probably this would solve your query.
I hope you won't mind reading it, brother. Always happy to help.
can we use calculateTable in the same,
where we have use calculte function
or we have to create a new table to use the calculateTable ?
Yes, you can. For example, see the following examples
Step 1: Sum of sales for the year 2021 = CALCULATE(
SUM(Sales[Amount]),
Sales[Year] = 2021
)
Step 2:
Now, you can futher filter the sales by specific regions as follows:
Total Sales for West Region = CALCULATE(
SUM(Sales[Amount]),
Sales[Year] = 2021,
CALCULATETABLE(
FILTER(Sales, Sales[Region] = "West")
)
)
Nice explanation.
But I think you should also discuss the use cases of each DAX function. For eg, so far to my knowledge, CALCULATETABLE is usually used inside the Iterating functions (such as SUMX, AVERAGEX, etc). Correct me if I'm wrong :)
And I can see that both equations give the same result (one using CALCULATE and another using SUMX, CACULATETABLE). Can you advise which one has better performance??
Thanks.
Hi Merida,
Yes, I agree, I should have mentioned some use cases as well which I am going to discuss next time. However, I have mentioned that "CalcfulateTable" DAX can be used either as an intermediate DAX function or to create a table.
Noted. Thanks for the reply.
What about performance between those two equations?
Hi in a calculatetable if i want to apply filter conditon for multiple cols den how ?
Please give us the clear picture of many to many relation n why we need to avoid and same explain cross filtering,what is cardinality i know this topics are basics but after watching reading many articles still little bit confused so try to post with few real time senarios
Coming Saturday I'll post a video regarding this.
Surly will wait and i have asked few questions for dax videos of 1 t o 4 pls answrit
You mean that , calculated table will generate new table ?
Yes, CALCULATETABLE would return a table of values
What are the limitations of calculate function?
1) When filter expressions are provided, the CALCULATE function modifies the filter context to evaluate the expression. For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function:
2) If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
3) If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.
The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It's required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context. This scenario can happen in a calculated column formula or when an expression in an iterator function is evaluated. Note that when a model measure is used in row context, context transition is automatic.
4) This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.