Forum Discussion
Grouping in Matrix is Strange
Anonymous wrote:d_gosbell thank you for the reply. Your suggestion will be perfect if my client does not require the Provider ID to be included in the Matrix visual at the beginning before Specialty .
Oh, sorry - I think I misunderstood what you were expecting to see in the second table.
I believe that what you are actually seeing is an issue with ALLEXCEPT where it strips off even implied crossfilters. Probably a safer pattern to use is a combination of ALL and VALUES
eg
Number of Unique Claims COMM = CALCULATE(DISTINCTCOUNT(Sheet1[ClaimNo]),ALL(Sheet1),VALUES(Sheet1[Specialty]),Sheet1[MA/COM]="COM")The SQLBI guys have a blog on this topic, the last few paragraphs summarize this issue
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
d_gosbell thank you for the reply. Your suggestion will be perfect if my client does not require the Provider ID to be included in the Matrix visual at the beginning before Specialty .
Super User
Anonymous wrote:d_gosbell thank you for the reply. Your suggestion will be perfect if my client does not require the Provider ID to be included in the Matrix visual at the beginning before Specialty .
Oh, sorry - I think I misunderstood what you were expecting to see in the second table.
I believe that what you are actually seeing is an issue with ALLEXCEPT where it strips off even implied crossfilters. Probably a safer pattern to use is a combination of ALL and VALUES
eg
The SQLBI guys have a blog on this topic, the last few paragraphs summarize this issue
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
Thank you d_gosbell for the formula. it works.. can you please explain to me what VALUE function does it this context?
Thank you again
- d_gosbell
Anonymous wrote:Thank you d_gosbell for the formula. it works.. can you please explain to me what VALUE function does it this context?
The VALUES function brings back the list of distinct specialities in the current filter context. So the ALL(Sheet1) strips all filters from the Sheet1 table and VALUES(Sheet1[Specialty]) then brings back just the current distinct set of Specialities.