DAX Measure That Contains Part of String

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I have a column in my table where the the client in some instances will be seen twice a year.

i wanted to know how can i write a DAXmeasure that contains a part of a string?

For example in my column i may have a value that says Jan, Jan/Jul. When i use the calculate function, is there a way i can write the filter part of the calculate function by incorporating if the field contains Jan to include both Jan & Jan/Jul? (Maybe a wildcard?)

image

Thanks

2

I really don’t recommend this. It’s seem to over complicate things and will mean your compensating for these unique values in all your formulas.

Is there a way to just create a calculated column with logic that re-creates these values like Apr/Oct.

Then you have then inside your date table and can use them as filter in many different ways.

3

Hi Sam,

Thank you for the response. Unfortunately I can’t seem to find a way of writing some logic into a calculated column. The Fact table has in excess of 44K rows and am not sure how to go about this.

When building the Model & Writing the DAX measures, i’m trying to understand what is good practice and your comments makes sense.

What I have currently done is used variables and then a switch statement based on the month selection.

VAR FEBAPPTS =
CALCULATE (
    [Adviser Clients],
    FILTER (
        Clients,
        Clients[Review Month] = "Feb"
            || Clients[Review Month] = "Feb/Aug"
    )
)

Here is a sample of the Fact Table:

image
image.png1398×62 4.8 KB

4

Right sorry I was thinking the data setup was a little different and requirement also.

Definitely look to avoid calculated columns in fact tables.

It’s an interesting one…I’m not sure you can in this case.

I’m just at the moment trying to think of a better way to do this.

I actually think you’re better off in the query editor having two column.

One column = Review date 1
Second column - Review date 2

To get these you would obviously need to split columns by the deliminator.

Then I think you could probably come up with some much simpler logic.

Can you catch my thoughts here.

I’m still wondering what calculations you actually need, but I’m hoping this different perspective might give you a better way to solve it.

Let me know if you think this might work.

Sam

5

Hi Sam,

I wanted to take share some feedback with you.

We review our clients annually but in some instances we review them half-yearly. What i am trying to achieve is to see what reviews we have projected in the future and manage expectations around that.

The review date unfortunately is not a date field but only contains a string with the month name. Eg: Jan, Jan/Jul

I was also trying to somehow link this Fact Table to my Dates Lookup table.

Before i asked the question this is how i went about building the measure:

Client Reviews = 
VAR JANAPPTS =
    CALCULATE (
        [Adviser Clients],
        FILTER (
            Clients,
            Clients[Review Month] = "Jan"
                || Clients[Review Month] = "Jan/Jul"
        )
    )
VAR FEBAPPTS =
    CALCULATE (
        [Adviser Clients],
        FILTER (
            Clients,
            Clients[Review Month] = "Feb"
                || Clients[Review Month] = "Feb/Aug"
               
        )
    )

I did that for each month and it was long, hence my initial question.

I considered you second suggestion (Splitting Columns) and I must say this suggestion was even more effective and i wanted to share the measure i wrote to achieve this:

Client Reviews= 
VAR FIRSTREVIEWMONTH =
    CALCULATE (
        [Adviser Clients],
        FILTER (
            VALUES ( Clients[First Review Month] ),
            COUNTROWS ( FILTER ( Dates, Clients[First Review Month] = Dates[Month] ) )
        )
    )
VAR SECONDREVIEWMONTH =
    CALCULATE (
        [Adviser Clients],
        FILTER (
            VALUES ( Clients[Second Review Month] ),
            COUNTROWS ( FILTER ( Dates, Clients[Second Review Month] = Dates[Month] ) )
        )
    )
RETURN
    FIRSTREVIEWMONTH + SECONDREVIEWMONTH

Thank you for the suggestion and I will add this to my notes :slight_smile:

6

Brilliant, that’s great. Thanks for sharing.