Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Count Number of IDs within a Call

Hi, I am trying to add up the number of calls that have so many IDs attached to them. So, for example 4 calls had 3 IDs attached to them, and so on. I am not sure how to display this in Power BI. I was hoping a Matrix would figure it out for me  but this is what I got: 

One call could have several IDs attached to it, or just one. So, out of the 7,617,384 calls, I'm trying to display that for example 100,000 of them had 3 IDs attached to them, 75,000 had 4 IDs, etc....can't figure out how to show this. 

Need Help

4 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    I created a new table (basically just number 1, 2, ... n), then used a measure to calculate the number of calls attached to an ID -

    Count of IDs = 
var temp = ADDCOLUMNS(VALUES('Table'[Ids]), "countIds", CALCULATE(COUNT('Table'[Ids])))
return COUNTROWS(FILTER(temp, [countIds] = SELECTEDVALUE(Frequency[Value])))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      vicky_ Thanks. Not sure if I've done this right since this is a new one for me. Here is the DAX based on what you said (I wasn't sure about what "Frequency[value]" was). 

       

      Count of IDs =
      var temp = ADDCOLUMNS(VALUES('table'[ID]), "countIds", CALCULATE(COUNT('table'[ID])))
      return COUNTROWS(FILTER(temp, [countIds] = SELECTEDVALUE('table'[CALL])))

       

      I got the error message: Couldn't load the data for this visual because DAX does not support comparing values of integer with values of text. I am not sure where in this DAX I need to fix this (the CALL and ID data must be stored as text because they both have letters in their codes). 

      • vicky_'s avatar
        vicky_
        Icon for Super User rankSuper User
        2 years ago

        SELECTEDVALUE('table'[CALL]) is the part that's not working. 
        In my DAX, i've created a new table called Frequency with a column called Value - that is what i'm using in my equation, not the call string. If you still have trouble, please add some sample data.