DAX expression for count of all regardless of slicer returns value I do not want

[Anonymous]

Hi Everyone

I am hoping someone can help me with this .

Note - I have lots of experience using excel and have just started learning DAX.

Situation: Want to create a column that shows count of all stores regardless of what the slicer selection is.

Example: I know the count of stores that I own

Count of stores I own = 
CALCULATE(
    COUNT('Door_Dash'[      Site  Number]),
    'Door_Dash'[                                Combination Name]
        IN { "MY BRAND NAME" }
)

What I don't know is the count of all stores in the table, whether I own them or not.

Here is what I tried:

AllStores = 
CALCULATE(
    COUNT('Door_Dash'[      Site  Number])
    ,ALL('Door_Dash'[      Site  Number])
)

This should work, but here is why this did not work.  My slicer is from table 'Store Attributes'[Number/Region].

I have created a relationship between 'Store Attributes'[Number/Region] and 'Door Dash'[Site Number].

The problem (I think) is that my 'Store Attributes' table only has site numbers for the stores I own.  It does not have site numbers for stores I do not own.

DESIRED OUTPUT: Count of all stores in the table, whether I own them or not, and not based upon slicer selection.

What is the correct DAX expression for this desired output?

[v-lili6-msft · Accepted solution]

hi, @Anonymous

Since My slicer is from table 'Store Attributes'[Number/Region]

If you want the formula to ignore slicer, you may add the condition into the formula like

AllStores = 
CALCULATE(
    COUNT('Door_Dash'[      Site  Number])
    ,ALL('Door_Dash'[      Site  Number])
    ,ALL('Store Attributes'[Number/Region])
)

If it is not your case, please share some data sample and expected output.  Do mask sensitive data before uploading.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft that worked great, so I was close just need an expert like you to help out!

-------

Ok, can you help me with one more question (staying within the theme of this thread):

I also need an expression that returns all stores IN the slicer that I do not own.  So far I have:

Count of stores I own = 
CALCULATE(
    COUNT('Door_Dash'[      Site  Number]),
    'Door_Dash'[                                Combination Name]
        IN { "MY BRAND NAME" }
)

And thanks to you I have:

AllStores = 
CALCULATE(
    COUNT('Door_Dash'[      Site  Number])
    ,ALL('Door_Dash'[      Site  Number])
    ,ALL('Store Attributes'[Number/Region])
)

And my slicer is:

What I need is an expression that counts all the stores IN the slicer that I DO NOT own.

What I have so far:

Count of stores I DO NOT own = 
CALCULATE(
	COUNT('Door_Dash'[      Site  Number]),
	'Door_Dash'[                                Combination Name]
		<> { "MY BRAND NAME" }
)

Unfortunately this is returning (Blank).  Any ideas?

[v-lili6-msft]

hi, @Anonymous

Does { "MY BRAND NAME" } is a data list or a fixed value?

If it is a data list, you need use NOT IN for your formula. like

Count of stores I DO NOT own = 
CALCULATE(
	COUNT('Door_Dash'[      Site  Number]),
	NOT('Door_Dash'[                                Combination Name])
		IN { "MY BRAND NAME" }
)

For example:

then 

Measure = CALCULATE(COUNTA(Table1[Column1]), NOT( Table1[Column1]) in {"b","c"})

If it is just one fixed value, you don't need to use { } in your formula. like

Count of stores I DO NOT own = 
CALCULATE(
	COUNT('Door_Dash'[      Site  Number]),
	'Door_Dash'[                                Combination Name]
		= "MY BRAND NAME" 
)

Also If you do want the formula to ignore slicer, you could add the condition into the formula

Best Regards,

Lin