cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gp93 Frequent Visitor
Frequent Visitor

CALCULATE and FILTER With Text column

Hi all,

 

I am quite new to DAX after mainly working with excel and I am struggling to get my head around something where the logic to me is sound.

 

minutesPercent = DIVIDE(
                    'bio agentStatus'[durationTotal],
                    CALCULATE(SUM('bio agentStatus'[statusDuration]),'bio agentStatus'[statusID])
                    ,0)

The above code works completely fine - however I have another column in the table that i have got from merging queries with another table - a text field called description which directly relates to statusID. Why is that this works but if i swap out statusID with description (so that i can display the descriptions on the visual) it doesn't?

 

Apologies if this has been posted before - I have looked around but couldn't find a solution that worked when i implemented it.

1 ACCEPTED SOLUTION

Accepted Solutions
gp93 Frequent Visitor
Frequent Visitor

Re: CALCULATE and FILTER With Text column

Hi,

 

Thanks for coming back to me - I think I must have not explained this very well as I managed to solve it with this:

minutesPercent = DIVIDE(
                    'bio agentStatus'[durationTotal],
                    CALCULATE(SUM('bio agentStatus'[statusDuration]),ALL('bio agentStatus'[bio status.description (groups)]))
                    ,0)

View solution in original post

2 REPLIES 2
Community Support
Community Support

Re: CALCULATE and FILTER With Text column

Hi @gp93,

 

Nope, you can't use math function to calculate with text value except count.


In my opinion, I'd like to suggest you create measure with calculate and text function to get current desktop, then drag it to tooltip field to display description.

 

Sample:

Measure =
CALCULATE (
    CONCATENATEX (
        VALUES ( 'bio agentStatus'[statusDuration] ),
        [statusDuration],
        ","
    ),
    VALUES ( 'bio agentStatus'[statusID] )
)

 

Reference link:

Text Functions (DAX) - With these functions, you can return part of a string, search for text within a string, or concatenate string values. Additional functions are for controlling the formats for dates, times, and numbers.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
gp93 Frequent Visitor
Frequent Visitor

Re: CALCULATE and FILTER With Text column

Hi,

 

Thanks for coming back to me - I think I must have not explained this very well as I managed to solve it with this:

minutesPercent = DIVIDE(
                    'bio agentStatus'[durationTotal],
                    CALCULATE(SUM('bio agentStatus'[statusDuration]),ALL('bio agentStatus'[bio status.description (groups)]))
                    ,0)

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors