Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DAX Count where

Hi All,   Coming from a T-SQL background I am not sure of the syntax I need.   I need two fields in the same visual which counts the rows form the table.   CoffeeMobile  with OrderSource =1 and...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Anonymous

    Hi,

    Which table are the stores and store locations in. If they are in a sole table, we need to create relationships with the current table. Then we add a calculated column with this formula.

     

    StoreCode =
RIGHT ( Table1[Store], 2 )

     

    If you can post more information, we can get more accurate formula. You can refer to the formula here for the moment. BTW, Ross73312 is quite right though missed something.

     

    CoffeeFront = CALCULATE(
       COUNT(
              'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_unique_id]
       ),      
       'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[promotion_id] = 20282,
       'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_order_src] = 0,
       StoreTable[Location] = “Broken Hill”,
       StoreTable[StoreCode] = “SA”
 )

    )

Anonymous's avatar
Anonymous
Not applicable
9 years ago

You were very close. You didn't need to add in the filter function as calculate does this already.

CoffeeFront = CALCULATE(
	COUNT(
		'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_unique_id]
	),	
	'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[promotion_id] = 20282,
	'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_order_src] = 0
)
CoffeeMobile = CALCULATE(
	COUNT(
		'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_unique_id]
	),
	'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[promotion_id] = 20282,
	'adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_order_src] = 1	
)