DAX Exercise for fun 😁 Part 3

[AntrikshSharma]

Here is a small Sunday fun challenge for all of you:

This is the data model containing only Products table:
DAX Challenge 3.pbix (35.6 KB)

This is the Code with result:

Challenge - You need to only show 710 for Contoso and not for other Products

Caveats -

1. You are not allowed to use IF as a quick and dirty solution
2. You cannot change the ADDCOLUMNS, & FILTER + VALUES part
3. You need to explain why the code is working

The code was written in DAX Studio, but you can do in Power BI.

[Hemantsingh]

HI @AntrikshSharma ,

The problem was a good exercise for refreshing ad understanding the evaluation context. With limitation you made it really fun.

Summary

Here is my Take

Explanation :
FILTER ( VALUES ( Products[Brand] ), Products[Brand] = “Contoso” ) this statement will ensure that a table of only only one value is returned i.e contoso.
Caclulatetable and keepfilter will ensure that the filter stays on Contoso when the values(Products[Brand]) is traversed.

[/spoiler]

Regards,
Hemant

[BrianJ]

@AntrikshSharma ,

Good one – I always enjoy these challenges from you. I was hoping to find a way to annoy you by using a simple COALESCE based solution instead of an IF statement to get you back for Rickrolling me earlier this week. But you smartly tightened the hatches in your rules and I couldn’t get it to work.

Note: I had originally written this using FILTER instead of the CALCULATETABLE function, but the solution only works with the latter.

This works because the way you had originally written Contoso Sales overrode the filter context from brand, but KEEPFILTERS restores that context subject to the condition in the KEEPFILTERS statement.

Thanks for putting this together and posting it.

– Brian

[Rajesh]

Hi @AntrikshSharma

Thanks for the Exercise. This kind of exercise’s really helps people who want to learn DAX.

This helps to understand below concepts.

1. Row Context
2. Filter Context
3. Context Transition
4. How CALCULATE works( Very power full function in DAX)
5. What happen when there is a context transition
6. Override and Intersect

Here is my solution

Adding calculate without filter arguments, we can keep filter coming from context transition

[UmaSuresh]

@Antriksh

I am a very beginner to DAX. Please advise whether it is the correct way of doing…

COUNTX(
ADDCOLUMNS(
VALUES(Products),“Contoso Sales”,[Total Products]), //[Total Products] = countrows(product)
filter
(values(Products[Brand]),
Products[Brand]=“Contoso”)
)

[BrianJ]

@UmaSuresh ,

If you put that code into Power BI, you’ll find that’s not a valid measure. Two major tips for you as you learn DAX:

1. Learn how to format your DAX code by indenting - this will be enormously helpful in identifying error, missing parameters, missing parentheses, etc. There are also a number of helpful tools to do this automatically, but when you’re first learning I think developing the habit of manually formatting as you write your DAX code is incredibly valuable.

2. For any DAX function you use, make sure you understand what the function returns (table or scalar) and what the form of the inputs is. DAX.guide is extremely helpful for this. As you can see from the DAX.guide entry, COUNTX returns a scalar, the first input is a table (can be a physical table like Products, or a virtual table created by table functions like ADDCOLUMNS, SUMMARIZE, DISTINCT, VALUES, etc.), the second input is an “expression” which means it can be a column reference (physical or virtual), a measure or a variable.

I absolutely applaud you jumping in and giving this a go, but one thing you should know is that @AntrikshSharma 's DAX challenges are usually quite advanced and deal with concepts and/or functions that as a beginner you will not have had much exposure to yet.

I hope this is helpful.

• Brian

[UmaSuresh]

Dear Mr.Brian

Thank you so much for your support and guidance. I understood how much it is important to understand DAX concepts well…Thank you for advising me on how to proceed for upgrading my skills.
I am confident that with all your vast knowledge and expertise in this field, I shall learn a lot. I will start following as per your above guidance.

Regards
S Uma

[AntrikshSharma]

BUMP so that others can participate too.

[Craig]

[spoiler]

This text will be blurred[/spoiler] Introduce a friendly and helpful DAX modifIer to create an intersection of the values and maintain the current filter context.

[AntrikshSharma]

@Craig What’s the current filter context here?

[EnterpriseDNA]

To all the contributors of this post.

Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others.

We are closing this thread due to inactivity.

We appreciate the initiative and your help in this group!