Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    7 years ago

    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)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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)