The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a large data set of transactions with information regarding Item, Stores and Month.
I need to calculate, for each row, what is the amount of unique months that are associated with that row.
For example for Item 10, Store 1200, there are 2 months (3 and 4), so the number I am looking to obtain is 2, for each row.
For Item 12, Store 1100, there are 6 transactions (rows) but only 3 unique months (1, 9 and 10) so the result I am looking for is 3 for each of the rows.
As you can see from the data, I am able to do the calculation in excel using countifs and sumif, but I cant seem to do it in Power BI. I have over 10 million transactions, so I cant do this calculation in excel.
Can someone help?
Thank you
Hi guys,
I have a large dataset (too big for excel) that I wanted to look at in Power BI.
Each row of data is a transaction. Item number, store and month of transaction.
I need to count the number of different months. So for example for Item = 10, Store = 1200, since a transaction occurs in month 3 and 4, the final result should be 2 (in red). For item 12 and store 1100, there are 6 transactions but spread out accros 3 different months, so the answer should be 3 (in red). The colomns after Month are the calculations that i used in excel to obtain the desired outcome.
Can someone recomend a solution in Power BI? I have over 10 million lines of such info.
Much appreciated!
You can use this formula
Column = CALCULATE ( DISTINCTCOUNT ( Table1[Month] ), ALLEXCEPT ( Table1, Table1[Item number], Table1[Stores] ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |