unique month calculation

[kpower2018]

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

[kpower2018]

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!

[Zubair_Muhammad]

@kpower2018

You can use this formula

Column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Month] ),
    ALLEXCEPT ( Table1, Table1[Item number], Table1[Stores] )
)