DAX newbie - how to calculate these two formulas

[LRMMF]

Hi,

imagine that you have 3 tables:
Table Fact Seller : seller_code, seller_name, manager_code
Table Fact Manager : manager_code, manager_name
Table Fact Product : product_code, product_name, seller_code

Relations have been made between keys

I want to calculate two aspects:

1) The number of active sellers. A Seller is active if it has any (1 or more) product in Fact Product.

i would like to know how i can i calculate this. it would be something like
=CALCULATE(DISTINCTCOUNT('Fact Seller'[seller_code]);[filter here]))

In [filter here] i need something like FILTER(COUNTROWS('Fact Product';'Fact Product'[manager_code])>=1), to get only the sellers from fact seller who have at least one product in fact product. I think i need to use the keyword VALUES() but i didn't find the correct syntax.

i would like to know what is better way or pattern for doing this.

2) The number of sellers with managers. Please note that when data is retrieved, if a seller has no manager the field manager_code has value "-1". So i can do this:
=CALCULATE(DISTINCTCOUNT('Fact Seller'[seller_code]);FILTER('Fact Seller';'Fact Seller'[manager_code]>=1))
So this is a filter inside the table. This works, but i would like to know if there is a better way or pattern for this.

Can you help out ?

kind regards

 

[mneumann]

Hello,

For #1, you should just be able to use the Fact Product Table as you just want to know the number of sellers who have a product in that table correct? So you can just use =distinctcount('Fact Product'[seller_code]) and it will count each seller code that appears in the table as 1 no matter how many times they appear.

For #2, as long as each seller is in the Fact Seller table only once you will not need distinct count, and you also shouldn't need to use a filter in your calculate, it should work with =calculate(countrows('Fact Seller'[seller_code]),('Fact Seller'[manager_code])>=1) or if no 0s for code =calculate(countrows('Fact Seller'[seller_code]),('Fact Seller'[manager_code])>0) and this will count the total rows in the Fact Seller table where the corresponding manager code is not -1.

 

[LRMMF]

Hi mneumann, first of all thanks for your help. I'm starting with DAX but i need to clarify some concepts for my daily work.

#1 i can agree with you, thought granularity is different between fact tables (seller daily Vs. product weekly).
How would you end this expression (as i've written on the 1st post)? =CALCULATE(DISTINCTCOUNT('Fact Seller'[seller_code]);........))

# 2 ok. i imagine that if the condition came from another table, DAX takes into account the existing relationship. one question, if there is more than one relation, how to force the use of a specific relation (such as order_date and return_date)

if you don't mind, can you give me an example of when to use FILTER() inside my CALCULATION(), and VALUES() also?

kind regards

 

[mneumann]

I'll try to go through these, but verify my answers on your data as I'm not sure I've ever run into some of these before and I'm testing on data that may be laid out different from yours.

For #1 if you wanted to write it that way to filter on the product table, I think it would work to write it as =CALCULATE(DISTINCTCOUNT('Fact Seller'[seller_code]),filter('Fact Product','Fact Product'[Seller Code]>0)) or at least that works on my end with slightly different data. I think what this is doing is tricking the Fact Seller table into filtering out Seller codes that don't exist in the Fact product table but I can't be sure as it seems kind of odd. I'm not sure if this is the best way to write this (probably not), but it gives me the same answer that the =distinctcount('Fact Product'[seller_code]) did on the same data, so it may work for your purposes.

edit for #1: countrows(values('Fact Product'[seller_code])) essentially returns the same result as the first formula I gave you which was distinctcount('fact product'[seller_code]) because it takes the seller code column and eliminates duplicates creating a new table of each distinct value and then counts the rows in the new table, so you could also use =CALCULATE(DISTINCTCOUNT('Fact Seller'[seller_code]),filter('fact product',countrows(values('fact product'[seller_code)) but that seems unnecessarily complicated unless its needed for some reason I am not grasping.

#2, if you have more than one relationship, you can use the userelationship function as a filter on your calculate argument so =calculate(countrows(orders),userelationship(calendar[date],orders[order_date])) gives you count of orders on specific date and =calculate(countrows(orders),userelationship(calendar[date],orders[return_date])) gives you returns on a specific date. Without the userelationship function dax will use the primary relationship, which you can look up and set in your relationships in your powerpivot window.

RE using filter in calculate, the short answer is only use it when you have to. I always try without filter first and then if the calculate won't run add filter, the long answer is you have to use filter for more complicated filters, so in above example 2 when your filter is manager code>0 calculate is fine, but if you were to get into using measures, etc in your calculate comparisons, you have to use filter.

Values, I don't really use much so I would have to defer to someone else to better explain when to use that, but actually looking into it a bit more see my edit above to answer one maybe that will work for you.

Hope this helps what you are doing.

 

[LRMMF]

mneumann, thank you for your excepcional response

Regards, from Portugal.

 

[LRMMF]

So for #1 i've tried these calculations and results where:
test1:=distinctcount('Fact Product'[sk_seller]) ==> 55882
test2:=CALCULATE(DISTINCTCOUNT('Fact Seller'[sk_seller]); Filter('Fact Product';'Fact Product'[sk_Seller]>0)) ==> 55161
test3:=CALCULATE(DISTINCTCOUNT('Fact Seller'[sk_seller]); filter('fact product';countrows(values('fact product'[sk_seller])))) ==> 55161

This must be some error on data, no? Probably i'm sticking with test1 syntax, where applied in my reports.

 

[scottsen]

Hi folks, I totally didn't read the thread, hope you dudes are good

One quibble. I suspect you are using DISTINCTCOUNT all over.. needlessly? Like DISTINCTCOUNT('Fact Seller'[seller_code]) ... should be just fine w/ COUNTROWS('Fact Seller') since the seller_code is unique in the 'Fact Seller' table?

This model looks REALLY straight forward Manager <--- Seller <--- Product

If the only measure I had was TotalProducts := COUNTROWS(Product) I could drop Manager and Seller on rows of my pivot table and it would "just work" for showing the # of products associated with both sellers and managers.

Total Active Sellers := CALCULATE(COUNTROWS(Sellers), FILTER(Sellers, COUNTROWS(RELATEDTABLE(Products)) > 0))
is probably how I would think about that measure. Though, honestly, I would just do an IsActive calc column on the sellers. There can't be that many rows of Sellers, and a true/false column is going to compress hardcore... it just won't hurt perf and simplifies things (and hey, maybe you want to have some charts/pivots that filter in/out the active sellers)

Your last measure it totally fine. Optionally you can use the "shortcut" flavor ...
Total Sellers With Managers := CALCULATE(COUNTROWS(Sellers), Sellers[ManagerCode] > -1)

 

[LRMMF]

I need to add a condition to "The number of sellers with managers", so active sellers is the previous metric and sellers have at least one product. Now the syntax i was trying was


**mneumann (based on what you have written for #1)
=CALCULATE(COUNT('Fact Seller'[sk_seller]);'Fact Seller'[sk_captain_incubation]>=1;countrows(values('Fact Product'[sk_seller]))>1)
error: calculation error on 'Fact Seller'[with Listing (Inc)]: expression True/False doesn't specify a column each expression used as a table filter must referentiate exactly 1 column.

=CALCULATE(COUNT('Fact Seller'[sk_seller]);'Fact Seller'[sk_captain_incubation]>=1;filter('Fact product','Fact Product'[sk_seller])>0)
error: tried filter with 'fact product' and 'fact seller' and the error was expression not valid or incomplete. review and fix the expression.

**scottsen
=CALCULATE(COUNT('Fact Seller'[sk_seller]);'Fact Seller'[sk_captain_incubation]>=1; FILTER('Fact Seller', COUNTROWS(RELATEDTABLE('Fact Product')) > 0))
error: expression not valid or incomplete. review the expression. COUNTROWS syntax is incorrect

Can you guys help me out?

edit: i've noticed scottsen code once more and tried
test=CALCULATE(COUNTROWS('Fact Seller');'Fact Seller'[sk_captain_incubation]>=1; FILTER('Fact Seller'; COUNTROWS(RELATEDTABLE('Fact Product')) > 0)) but this crashes my Excel.

 

[scottsen]

Crashes Excel! Nice

Is it possible to share the workbook (on dropbox or whatever) ?

 

[LRMMF]

Crashes Excel! Nice

Is it possible to share the workbook (on dropbox or whatever) ?

Hi, Scottsen, i would like to share because i need to solve this, but i can't...

Today it doesn't crash, but i don't get any result (it remains blank). I'll risk and say that it happened because i was almost run out of disk space.

From the 3 formulas i've presented (yours and 2 from mneumann), can you say which ones should give the correct result?