Forum Discussion

NAV_81's avatar
NAV_81
Frequent Visitor
5 years ago

DAX - Count with Multiple Filters, Causing Memory Issues.

I am pretty new to DAX and am trying to do a count on some invoicing costs issues, there are some filters that need to apply to show what a true cost issue is. I have got the below to work the way I wanted but I am getting memory issues and the visuals will only work when a store is selected on my report. Any ideas on how to optimize this would be greatly appreciated.

 

Cost Issue Count = CALCULATE(COUNTROWS(Invoices),FILTER(Invoices,[Cost Issue]<0),FILTER(Invoices,[Est GP %]<0.20),FILTER(Invoices,[CurrentSellInc]>0),FILTER(Invoices,[Invoice Status]<>"Killed"),FILTER('POS Item Details',[ModuleItem]="F"),FILTER('POS Item Details',[PillarName]<>"Prescriptions"),FILTER(Invoices,[CurrentSellLocationCode]<>"CLR"))
Need Help

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    5 years ago

    Hi NAV_81 

    Its bit difficult to say if this issue is due to measure only since memory issue can be related to your data model as well. If you could share your precised data model so we might help you more specifically. 

    But in the mean time can you try below code?

    Cost Issue Count =
CALCULATE (
    COUNTROWS ( Invoices ),
    FILTER (
        Invoices,
        [Cost Issue] < 0
            && [Est GP %] < 0.20
            && [CurrentSellInc] > 0
            && [Invoice Status] <> "Killed"
            && [CurrentSellLocationCode] <> "CLR"
    ),
    FILTER (
        'POS Item Details',
        [ModuleItem] = "F"
            && [PillarName] <> "Prescriptions"
    )
)

     

    Thank you,

    Samarth

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Solution Specialist
    5 years ago

    Can you try this?

    Cost Issue Count =
CALCULATE (
    COUNTROWS ( Invoices ),
    FILTER (
        Invoices,
        [Cost Issue] < 0
            && [Est GP %] < 0.20
            && [CurrentSellInc] > 0
            && [CurrentSellLocationCode] <> "CLR"
            && [Invoice Status] <> "Killed"
    ),
    FILTER (
        'POS Item Details',
        [ModuleItem] = "F"
            && [PillarName] <> "Prescriptions"
    )
)