Forum Discussion
Calculate using OR from Dimensions
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.
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.