Exclude Dimension for a Measure

[sandeepk66]

Hi Geeks,

I m trying to Filter a Measure(Sum of SALES) based on caluclation on Dimesnions GROUPID='PHL' AND ITEM='1003'

Basically, Needs the below SQL condition in DAX:

SUM(CASE WHEN NOT([GROUPID]='PHL' AND [ITEM]='1003') THEN SALES END)

[Ashish_Mathur · Accepted solution]

Hi,

From what i understand, you wish to sum the sales after ignoring PHL from the GroupID column and 1003 from the Item column.  If my understanding is correct, then try this

=CALCULATE(SUM(Data[Sales]),Data[GroupID]<>"PHL",Data[Item]<>1003)

Please note that i have assumed the Item column to be a numeric column.  If not, then wrap 1003 in double quotes.

[emadrigal]

In power BI if you are using DAX you need to use calculation calculate formula for example:

calculate formula take and expression and a filter

Excluded Dimension = calculate(sum(sales), [GROUPID]="PHL" and [item]=1003)

[emadrigal]

if you are creating a measure with dax you need to use double quotes and use calculation for example:

excluded Dimension = calculate(sum(sales),[groupID]="PHL" and [item]="1003")