Forum Discussion
DISTINCTCOUNT
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")
)
Solution Specialistwell, 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
- vapid128
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")
)
Thanks, I'm absolutely gratefull for your answer.
For my own learning I'll try to recap what is going on, please correct me if I'm wrong:
The filter argument:
LEFT ( Tbl[Name], 1 ) = "M"
Is changed by the DAX engine to
FILTER ( ALL ( Tlb[Name] ), LEFT ( Tbl[Name], 1 ) = "M")
And for this reason DISTINCTCOUNT ( tbl[name], LEFT ( Tbl[Name], 1 ) = "M" ) will return the same value for every tbl[Name] value.
But your filter argument is:
FILTER ( Tbl[Name], LEFT (Tlb[Name], 1 ) = "M" )
= {"Montageforbrug", "Montageforbrug output"}
And when this table is joined with the filter context, ex. "Køb" no rows are returned, the measure will return a blank, and the row "Køb" is not shown.