CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9714

How is measure able to filter dimension via fact table?

I have 2 tables: dimProduct and factSales

Dim table has productid, name, category

Fact table has salesid, productid, status, amount, paiddate

I have a table visual that shows the status from the fact table. Against each status I want to show the count of products and the count of distinct category.

CountProducts=DISTINCTCOUNT(FACTSALES[PRODUCTID])

CountDistinctCategory=DISTINCTCOUNT(dimProduct[category])

How to correct this?

Upvotes: 0

Views: 1606

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

Set the cross-filter direction for your relationship to 'Both', so that filtering can also propagate from the fact table to the dimension table.

Alternatively, using DAX:

CountDistinctCategory =
CALCULATE(
    DISTINCTCOUNT( dimProduct[category] ),
    TREATAS(
        VALUES( factSales[productid] ),
        dimProduct[productid]
    )
)

Upvotes: 0

Related Questions