Forum Discussion
Calculate using OR from Dimensions
I have a fairly simple model with 1 Fact table and 2 Dimensions.
Fact Table Sales:
SalesID, ProductID, StoreID
ProductDim
ProductID, ProductName
StoreDim
StoreID, StoreName
What I need to do is write a dax that will calculate the number of distinct SaleIDs, when either dimension is selected. e.g. If I seled ProductName="Gym Paints" and StoreName="Walmart", I'll get the count of SalesIDs that contain either Gym Paints ProductName or Walmart StoreName.
I have so far tried:
SalesTotal :=
CALCULATE(DISTINCTCOUNT(Sales[SalesId])
-- various forms of FILTER, but none have worked.
)
I know this is not ideal solution, we are forced into this because other solutions have been performing slowly.
Thanks!
Sorry, I missunderstood. You can’t do what you want if you have 2 active relationships. Relationships from 2 dim tables are logical AND. So either remove the relationships or make them inactive. Then write something like
measure = VAR prod = MAX ( prod[id] ) VAR Store = MAX ( store[ID] ) VAR prodsales = FILTER ( sales, sales[prod id] = prod ) VAR Storesales = FILTER ( sales, sales[store id] = store ) VAR allsales = UNION ( prodsales, storesales ) // includes 2 copies of rows that are both VAR alldistinctsales = DISTINCT ( allsales ) // removes double count of rows that are both RETURN CALCULATE ( [total sales], Alldistinctsales)I think this will work. I haven’t tested it.
3 Replies
You don’t need calculate. Just write this
DISTINCTCOUNT(Sales[SalesId])
Thanks MattAllington ,
Note I want to apply two dimensions to the calculation though. If I simply do:
measure := distinctcount(Sales[SaleId]) and apply two dimension filters, it will count only rows where the two dimension rows are applied.
I want to acheive an OR condition where either of the two dimensions are applied. I.e. distinctcount of salesid when ProductA OR storeB is selected in the dimensions.
Thanks!
Sorry, I missunderstood. You can’t do what you want if you have 2 active relationships. Relationships from 2 dim tables are logical AND. So either remove the relationships or make them inactive. Then write something like
measure = VAR prod = MAX ( prod[id] ) VAR Store = MAX ( store[ID] ) VAR prodsales = FILTER ( sales, sales[prod id] = prod ) VAR Storesales = FILTER ( sales, sales[store id] = store ) VAR allsales = UNION ( prodsales, storesales ) // includes 2 copies of rows that are both VAR alldistinctsales = DISTINCT ( allsales ) // removes double count of rows that are both RETURN CALCULATE ( [total sales], Alldistinctsales)I think this will work. I haven’t tested it.
