Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Aaronjoem
Frequent Visitor

DAX - Search & Find question - post per Ruth (youtube)

Issue - Trying to sum (amount) with the following filters. What I have so far if you were doing it in excel the normal way
1. Calculate (Amount) - easy
2. Filter column (A) on the word Actual (easy)
Issue start here -
3. Filter column (B) where the first two numbers start with (27) and there should be a total of six numbers. Anything less or more shouldnt be counted

Thanks for any help
1 ACCEPTED SOLUTION

Hi @Aaronjoem ,

Maybe you can try to use following measure formula if it suitable for your requirement:

formula =
CALCULATE (
    [Total Resource Amount],
    FILTER (
        ALLSELECTED ( OneLink_Report ),
        OneLink_Report[Ledger] = "ACTUALS"
            && LEFT ( OneLink_Report[Child Activity], 2 ) = "27"
            && LEN ( OneLink_Report[Child Activity] ) = 6
    )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @Aaronjoem ,

It will be help if you share some sample data and expected result for test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Here is a copy of my code the long way

 

CALCULATE([Total Resource Amount],OneLink_Report[Ledger] = "ACTUALS", OneLink_Report[Child Activity] = 275223 || OneLink_Report[Child Activity] = 272000 ||OneLink_Report[Child Activity] = 270533 ||OneLink_Report[Child Activity] = 271000 ||OneLink_Report[Child Activity] = 275119 || OneLink_Report[Child Activity] = 270528 || OneLink_Report[Child Activity] = 274000 || OneLink_Report[Child Activity] = 270500 || OneLink_Report[Child Activity] = 270000 || OneLink_Report[Child Activity] = 275116 || OneLink_Report[Child Activity] = 275000 || OneLink_Report[Child Activity] = 274100 ||OneLink_Report[Child Activity] = 271300)

 

 

How can I make it simpler? So far here is what I came up with. Only works 90% still include this number 27000

 

CALCULATE([Total Resource Amount],OneLink_Report[Ledger] = "ACTUALS", LEFT(SEARCH("27*",OneLink_Report[Child Activity],,0)=1,6))

Hi @Aaronjoem ,

Maybe you can try to use following measure formula if it suitable for your requirement:

formula =
CALCULATE (
    [Total Resource Amount],
    FILTER (
        ALLSELECTED ( OneLink_Report ),
        OneLink_Report[Ledger] = "ACTUALS"
            && LEFT ( OneLink_Report[Child Activity], 2 ) = "27"
            && LEN ( OneLink_Report[Child Activity] ) = 6
    )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
AlB
Super User
Super User

Hi @Aaronjoem 

Try this:

 

Measure =
CALCULATE (
    SUM ( Table1[Amount] );
    Table1[ColumnA] = "Actual";
    INT ( DIVIDE ( Table1[ColumnB]; 10000 ) ) = 27
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.