cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Help : DAX optimization

Sorry, my bad, forgot the CALCULATE in the ADDCOLUMNS:

 

Test Measure =
VAR _curdate =
    MAX ( 'Date'[Date] )
VAR _prevdate =
    DATE ( YEAR ( _curdate ), MONTH ( _curdate ) - 12, DAY ( _curdate ) )
VAR filtered_table =
    CALCULATETABLE (
        FILTER ( Sales, Sales[Bill Flag] = 1 ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] > _prevdate && 'Date'[Date] <= _curdate )
    )
VAR _table =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( filtered_table, [LoyaltyId], [Bill Flag] ),
                "BuyingVal", CALCULATE ( SUM ( Sales[NetSaleAmount] ) ),
                "BuyingFreq", CALCULATE ( DISTINCTCOUNT ( Sales[Original Bill Num] ) )
            ),
            [BuyingVal] > 4500
                && [BuyingFreq] < 3
        )
    )
RETURN
    _table

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Baskar Super Contributor
Super Contributor

Re: Help : DAX optimization

Thanks @ImkeF , FYI I have already added calculate in addcolumn earlier. No luck

Super User
Super User

Re: Help : DAX optimization

I don't see what else I can do without any data to compare against here.

So you might consider creating some sample data with source and desired outcome so we can narrow down the problem.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Baskar Super Contributor
Super Contributor

Re: Help : DAX optimization

@ImkeF  Here i have attached power bi file with sample data. 

 

Example : 

For  Jan 2015 expected result is 80, but we are getting 3. 

 

Refer the first tab for raw data. 

Super User
Super User

Re: Help : DAX optimization

THanks, but I cannot spot the measure that returns the correct figures.

What's its name?  - and if it isn't included, please include it as well.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
Super User
Super User

Re: Help : DAX optimization

@Baskar  - The following will give you 80 for Jan 2015 in your file:

Checking 2 = 
var _cur = MAX('Date Master'[Start Date])
var _pre = DATE(YEAR(_cur),MONTH(_cur)-12,DAY(_cur))
var _filter = 
CALCULATETABLE(
    Orders, 
    Orders[Ship Mode]="Standard Class",
    FILTER(ALL('Date Master'),'Date Master'[Start Date] >_pre && 
            'Date Master'[Start Date] <= _cur))
var _summed = 
SUMMARIZE(
    _filter,
    [Customer ID],
    "sales",SUM([Sales]),
    "count",DISTINCTCOUNT([Order ID])                       
)
return  COUNTROWS(filter(_summed,[sales] >1000 && [count] <5))
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 424 members 4,893 guests
Please welcome our newest community members: