Forum Discussion

J_Mug's avatar
J_Mug
Helper I
4 years ago

Count IF summarized without calculated table?

Hi,

 

I want to count the total number of stores that exceed X number of transactions (classic count if in excel). My current solution for this problem is this one:

 

  • I have a fact table with the store code and the details of every transaction (with millions of transactions). I created a calculated table, that summarizes the number of transactions by store code: CALCULATETABLE(SUMMARIZE('Transactions','Transactions'[Store ID],"Count",COUNT('Transactions'[Store ID])))
     
  • Then for this table i create a measure: Stores_Above_Threshold = Calculate(Count('Transactions'[Store ID]),FILTER('Transactions','Transactions'[Store ID] >= 200000)
     
  • Then a card visual displays the number of stores that exceed that transaction number

I want to know if I can do this in DAX avoiding the use of a calculated table and allowing the measure to be filtered by date (If I add a date to the summarize formula then it would not give the correct count, as Store ID would be repeated by the total number of dates it has transactions in).

 

Thanks in advance,

 

Kind regards,

2 Replies

    • J_Mug's avatar
      J_Mug
      Helper I

      Hi, 

       

      I cant upload files and my organization doesnt allow access to share hosting sites so I cant share.

       

      I can show you example pictures with the same configuration