Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to use a column of unique values to count the number of times the values appear in another table. My data looks like the following:
The values to be looked up:
The values to be looked up against look like this, where the column I am interested in is the 2nd one, "Post Number."
What I hope to acheive here is a to be able to create a table (a visual) where I can see a count of how many times each value appears. The tricky part here is of course that I am not looking for an exact match, just whether or not the value is contained somewhere within the cell.
Example: The value "2200" appears 3 times in the above data set, so I would expect to see "3" returned.
From there I plan to filter based on the 3rd and 4th columns, Active, and Approved on the visual level.
I have tried serveral methods without any luck. Any advice?
Solved! Go to Solution.
Perhaps something like:
Measure = VAR __pn = MAX(Table9[PN]) VAR __pnLength = LEN(__pn) VAR __table = ALL('Table10') VAR __table1 = ADDCOLUMNS(__table,"__Count",(LEN([Post Number]) - LEN(SUBSTITUTE([Post Number],__pn,"")))/__pnLength) RETURN SUMX(__table1,[__Count])
Perhaps something like:
Measure = VAR __pn = MAX(Table9[PN]) VAR __pnLength = LEN(__pn) VAR __table = ALL('Table10') VAR __table1 = ADDCOLUMNS(__table,"__Count",(LEN([Post Number]) - LEN(SUBSTITUTE([Post Number],__pn,"")))/__pnLength) RETURN SUMX(__table1,[__Count])
Whoa, thank you very much!
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |