Forum Discussion
DISTINCTCOUNT
Hi
Can someone help me understand the result in the lowest visual?
Why does the Tbl[Name] rows not form Filter context to make [#Name with M] zero on all other lines than the two starting with "M"?
All you need to do is change your boolean expression to filter table.
Calculate(
distinctcount('Tbl'[name[),
filter('Tbl'[Name],LEFT ( Tbl[Name], 1 ) = "M")
)
6 Replies
https://docs.microsoft.com/en-ca/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument
"Here's an improved measure definition, which uses a Boolean expression instead of the table expression. The KEEPFILTERS DAX function ensures any existing filters applied to the Color column are preserved, and not overwritten."vapid128 Thanks for your answer but:
- The filter argument IS a boolean expression: LEFT ( Tbl[Name], 1 ) = "M"
- Using KEEPFILTERS doesnt change anything, that is, only the tabel showing both columns tbl[Nr] and tbl[Name] will show the correct result: the two rows where tbl[Name] are starting with "M" both rows having [#Names with M] = 1. When only the column tbl[Name] is shown in the table visual, the problem remains: all tbl[Name] are shown with [#Name with M] = 2
well, in a calculated function with a boolean expression
In 1st case, the filter of the values is like [Nr]=8 and LEFT ( Tbl[Name], 1 ) = "M"
In 2nd case , Tbl[Name] = "xxx" and LEFT ( Tbl[Name], 1 ) = "M"
That is same as Tbl[Name] ="aaa" and Tbl[Name] ="bbb".
Therefore, power bi system will change your dax function to
Calculate(
distinctcount('Tbl'[name[),
filter(all('Tbl'[Name]),LEFT ( Tbl[Name], 1 ) = "M")
)
Thanks again, vapid1238, I'm learning from that.
Allthough in 1st case, I think you mean the filter is like Tbl[Nr]=8, Tbl[Name]="xxx" and LEFT (Tbl [Name], 1 ) = "M" ?
Can you help me write a working DAX, showing only the two columns Tbl[Name] and [#Names with M], the result beeing as in the 1st case? KEEPFILTERS are fine i a solution, but in my real world scenario though, I cant use it, as it is not available in my current SSAS version = 2016.
Kind regards, Henrik