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 w...
Need Help
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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      vicky_ I appreciate your help. Can you share with me the DAX for the table you created? I think I'm confused because in the illustration at the top it looks like you have a column Frequency and a column Value. I am not sure how those two are interacting with each other (if they are). Thanks!