cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Calculate the number of Items with filters in DAX

Hi everyone,

I need to display the number of Items with certain filters:

- Item Group is "GLO"

- Items have been purchased within last 12 months at least 10 times ( so, PO number is >= 10 and date filter is in the last 12 months)

I am trying to create a calculated column to see, how many items meet those criterias.

When I put those filters on a table visual, I can clearly see the correct number of items. But I face a problem when I create DAX formula:

Items of interest = CALCULATE(DISTINCTCOUNT('BaseData'[Item]), FILTER(PurchaseOrder, PurchaseOrder[orderdate] >= DATE(YEAR(TODAY())-1,MONTH(TODAY()), DAY(TODAY()))), PurchaseOrder[orderdate] <= TODAY(), FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"))

Until here the formula work perfectly showing how many GLO items have been purchased within last 12 months.

BUT when I try to add a filter related to number of Purchase Orders, I get a problem. Even after adding 1 more filter, the number doesn't change (meaning I am doing it wrong).

Items of interest = CALCULATE(DISTINCTCOUNT('BaseData'[Item]), FILTER(PurchaseOrder, PurchaseOrder[orderdate] >= DATE(YEAR(TODAY())-1,MONTH(TODAY()), DAY(TODAY()))), PurchaseOrder[orderdate] <= TODAY(), FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"), FILTER(PurchaseOrder, DISTINCTCOUNT(PurchaseOrder[POnumber]) >= 10)))

Do you know how to solve that problem?

And also, how to make it a True/False slicer? Meaning that I need to apply it a visual (to show visual only for those items of interest).

2 REPLIES 2
Super User

@Anonymous , With help from date table joined to your date

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH),FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"))

or

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-12,MONTH),FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"))

Then you need a measure

countx(Values([PurchaseOrder[POnumber]), if([ROlling 12] >10, PurchaseOrder[POnumber], blank() ) )

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Anonymous
Not applicable

Thanks @amitchandak

But there is no Sales data in this table or any other tables that I am using for this report.

Is it possible to avoid sales data somehow and receive the same result with number of items?

Thanks!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.