cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gp93
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
Highlighted
gp93
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
Highlighted
gp93
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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors