DAX countrows accounting for other filters

[runarlan]

Hi!

Let's assume you have a fact table, A, and two dimension tables, B & C. For all rows in dimension B I want to have a count on how many times the row is referenced in fact table A. I could do that by this DAX formula in table B: COUNTROWS(RELATEDTABLE(A)).

Now I wan't to expand this formula to also account for filters I have in table C that affects table A, and filters directly in table A. So, given the filters in table C and A, how many times is each row in B referenced in table A. How can I do this in DAX? 

[Anonymous]

Could you tell us the relationship between table C and table A. If you have implemented star schema then the filters will work automatically. 

For example :

I have created a measure in Product table,

Fact Rows := COUNTROWS(RELATEDTABLE(Sales))

This measure will take all the filters applied for both Sales and Date tables in to consideration (it should satisfy your needs) before displaying result. Filter context will automatically passes through relationships. 

[runarlan]

Thanks for the answer nvpraveenyakkal!

The relationship is a star schema, exactly like your picture, where Sales is A, Product B, and Date C. 

But I wasn't clear enough in my question. I'm not making a measure. I'm making a column in table B where I'm counting related rows in table A.

I wan't to do this, because I wan't to know what's the maximum amount of related rows in A for a row in table B, so I could use that in a measure to only sum rows in table A that is related to a row in table B where the rows in table B have this maximum number. 

To explain it with an example:

Table B is products.

Table C is suppliers.

Table A is bids/proposals (a price for a product given a supplier). 

I wan't to compare suppliers' offers, but the problem is that not all suppliers proposes all products. So a sum of their total price would not be correct if they don't offer the same products. Therefore I want to have a column in the Products table that finds how many offers they are a part of given all filters. So if I filter my suppliers table to three suppliers, and one product is offered by all the suppliers, I want to only sum the prices for all offers that are a offer for a product that is offered by all suppliers. 

Sorry if this was more confusing... 

[Anonymous]

I think you explained as much as possbile even though I do have some questions. I don't want to make it complicated again by asking questions and waiting for the response (might be bit hard to communicate in this scenario). 

Instead post sample data how the data looks like and the result you are expecting, that might give a better idea. 

[runarlan]

Suppliers:

Supplier_ID	Supplier_Name
1	Supplier A
2	Supplier B
3	Supplier C

Products:

Product_ID	Product_Name
1	Product A
2	Product B
3	Product C
4	Product D

Offers:

Offer_ID	Supplier_ID	Product_ID	Price
1	1	1	5
2	1	2	4
3	1	3	6
4	1	4	4
5	2	1	5
6	2	2	1
7	2	3	10
8	3	1	7
9	3	2	3
10	3	4	4

This means that Supplier A offers all 4 products, but Supplier B and C only offers 3 products (but not the same). So if I want to compare the offers, I need to only compare the common products of the suppliers I want to compare. Meaning if I compare every supplier, I can only look at Product A and B, giving Supplier A a total price of 9, supplier B a price of 6 and supplier C a price of 10. Supplier B is the best when comparing every supplier.

If I want to compare only supplier A and B, I can use products A, B and C in the comparison, since both suppliers can deliver those three products. Then the total price for supplier A is 15, and for supplier B the price is 16, so when comparing only supplier A and B, supplier A is the best. 

So I would like a model where I could filter on suppliers, and then getting the total price for each of those suppliers for the products that all those suppliers can deliver.

My thought was then to add a column Y in the Products-table, counting how many times each product is related in the Offers-table, and adding a measure X that finds the maximum times a product is related. Then I would add a new column Z in the Offers-table that takes the value from the Price-column if the related Product has the same value in column Y as the measure X, and 0 else. But the column Y is not behaving like I was expecting, it just counts related rows in the Offers-table without using the filters I have added.