Solved: Re: Assistance needed with a Measure/Query

mikekolba · ‎04-11-2019

I currently have the following measure

Current Revenue = CALCULATE(

SUM(Query1[Column2]),

FILTER(Query1,SEARCH("Current",Query1[Source.Name],,0)),

FILTER(Query1,Query1[Column1]="Revenue")

)

it performs a search in the source.name column for a file name that contains "current" and a further filter on another column where it looks for "revenue" and then does a calculation and sum of the value returned.

I need to do an idential but instead of doing a sum, i need to search in the source.name column for a file name that contains "current" and a further filter on another column where it looks for "_" and then return the value that it found which is a text value and won't be sum or math calculation.

How would i modify my query above to do the desired Measure?

parry2k · ‎04-11-2019

@mikekolba you can change like this.

Current Revenue = CALCULATE(
MAX(Query1[Column2]),
FILTER(Query1,SEARCH("Current",Query1[Source.Name],,0)),
FILTER(Query1,CONTAINSSTRING( Query1[ColumnToSearch], "_")
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k · ‎04-11-2019

@mikekolba you can change like this.

Current Revenue = CALCULATE(
MAX(Query1[Column2]),
FILTER(Query1,SEARCH("Current",Query1[Source.Name],,0)),
FILTER(Query1,CONTAINSSTRING( Query1[ColumnToSearch], "_")
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.