DISTINCTCOUNT

[Anonymous]

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"?

[Anonymous]

@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

[vapid128]

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")

)

[Anonymous]

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 · Accepted solution]

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")

)

[Anonymous]

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.

[vapid128]

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."