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 the other 'CoffeeFront' Ordersource = 0  with both using promotion id 20282 where the store like '%SA' and store in ('Broken Hill').

 

I have written the following DAX, which is missing the store requirements.

 

CoffeeFront = CALCULATE(COUNT('adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_unique_id]),FILTER('adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv','adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[promotion_id] = 20282),FILTER('adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv','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]),FILTER('adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv','adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[promotion_id] = 20282),FILTER('adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv','adeadhoc_20170510_203451_gma_mobile_ordering_master_filtered_de20170510_csv'[order_order_src] = 1))

Any aisstance would be great, Thanks

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”
 )

    )

3 Replies

  • 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	
)

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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”
 )

    )

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Anonymous

     

    Hi,

     

    Is your problem solved? What can I do for you?

     

    Best Regards!

    Dale