Forum Discussion
J_Mug
4 years agoHelper I
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
- ghoshabhijeetSolution Supplier
J_Mug Please provide sample non-confidential data. Thanks !
- J_MugHelper 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