New Help with a forumula, Kind of new to DAX and coming from SAP Crystal

[dkel6177]

I am sure this is pretty simply, but I appreciate the help. I have a table of information that I want to performa a condition summary measure within. I want to conditionally sum the currency in a field based on whether or not certain text appears in another column. The standard = does not work becuase I need it to search for specific text within a series of string. Can someone help me out. 

[Sean]

@dkel6177

Measure =
CALCULATE (
    SUM ( TableName[Currency Column] ),
    TableName[Condition Column] = "Condition 1"
)

[dkel6177]

Thanks for the help,... but in the condition 1 area where I would search for text... will is look for a wildcard value. For instance... I want to return a summary on a column where the word "budget" might appear amongst other values or embedded within other text. Like "Admin Budget"

[Sean]

How are you going to Visualize this?

[dkel6177]

In a Card. Just trying to show Total Budgeted based on Whether Budget appears in the text or Spent. I need to search for this work in the Budget Node Text.

[Sean]

Try this...

Measure =
CALCULATE (
    SUM ( TableName[Currency Column] ),
    ALLEXCEPT ( TableName, TableName[Condition Column] )
)

[Sean]

This should be enough for the Card

Sum of Spent =
IF ( ISBLANK ( SUM ( 'PD SUMMATY'[Value] ) ), 0, SUM ( 'PD SUMMATY'[Value] ) )

It will show Overall Total when nothing is selected and will filter based on the slicer or if you click on the other charts.

Wild card? Are you looking for user input? I mean user to type and search???

[dkel6177]

I can get what I want if I just filter the visualization, But I need it as a measure so that I can use it for additional calculations. I thought there might be a way to this with a wildcard.

Sum of Spent = SUMX(FILTER('PD SUMMARY', 'PD SUMMARY'[Budget Node] = "Spent"), 'PD Summary' [Value])

Where "Spent" could be a wildcard like "*Spent*"? Can't seem to get it to search out an filter the calculation where it see's "Spent" anywhere in the text.

[Sean]

@dkel6177 I'm gonna test this later... In the meantime this may help you...

https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

[Sean]

@dkel6177 Create a Conditional Column (Category) in the Query Editor as shown in the picture

Then Duplicate and Rename the Original (Budget Node) column

and Replace Values (first Budget and then Spent) as in the picture

Now as you can see in the picture you can create any Measures and/or Slicers to display the data!

Hope this helps!

@ImkeF no need for M with the new conditional column option

By the way Marco and Alberto have a new book coming out in October.

[ImkeF]

@Sean very nice! (also your mini-tutorial)

What will the book be about / which focus/perspective will they take this time?

[Sean]

@ImkeF No details yet...  I just got an email from Amazon that I can pre-order it...

[dkel6177]

Just gives me the total without searching for the text I need. Just trying to calulate values based on whether or not the word "Budget" or "Spent" appears in that column. However there is string in front of it.