DAX Fridays! #69: How to write a DAX measure (Part 1)

In today’s video I will show you how to create a bit more complex calculation using DAX. We will calculate the average number of categories per customers.

Here in Part one, we will calculate it using Summarize, and then summarize and addcolumns and finally, summarize and addcolumns and calculate to get the right totals.

In part 2 (live on Friday 2018-02-23): we will use AverageX only and

In part 3 (live on Friday 2018-03-02): we will remove the bi-directional relationship and calculate it without it.

Enjoy!

PREVIOUS VIDEO:

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

************

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#daxfridays #curbal #SUBSCRIBE

[mirzotti]

Hi Ruth, thanks for an other great video! I was taught by Alberto Ferrari that we may never use bi-directional filtering so every time I want to use it I see him giving me the looks :). So for this I always use CROSSFILTER function within CALCULATE. This way bi-directional filtering is only active withing current measure. I have done everything as you did only I added CROSSFILTER function within CALCULATE. Also it made the total issue that you mention unintentionally dissapear.
ADDCOLUMNS (
SUMMARIZE (
Orders,
Orders[CustomerID]
),
"no of categories",
CALCULATE (
DISTINCTCOUNT(Products[CategoryID]),
CROSSFILTER (
Order_Details[ProductID],
Products[ProductID],
Both
)
)
)

[mnowako]

Brilliant! I was just looking for sth like that! Many thanks Ruth!

[gagansingh]

Hi Ruth as there is no relationship between product & order table then how do you get different distinct count for category ID from product table I have downloaded your file the distinct no.of categories is 8 for each customer ID?

[andrecavasotero]

I always watch videos on the weekend. So I'll have a great Sunday with Fridays DAX.

[Victor-ollo]

Hi Ruth, DAX Sundays sounds good to me :-) but keep in mind that also Power BI Pros require a weekend to relax....