why does a measure changes its result when lines on a Table are selected and how to prevent that ?

[Anonymous]

Hello,

I have a dashboard with 2 visuals : one Table and one Card.

The table displays the content of "Query A".

In that query A, I have added a Measure :

NbNumberInDoubloon = COUNTROWS('Query A')

It works OK, except when lines are selected in the Table visual, the Card changes its display. It recalculates according to the lines selected and not anymore to the whole content of "Query A".

If it matters, this measure is added within the query, not in a separate query/table that would contain only the measures (I think it is possible that's why I say that).

The code of my measure refers to "Query A" in its globality, so why does it change when lines are selected ? Why are they linked ? And how can I make sure it displays all the time the number of rows of the whole table ?

Thanks for your help,

[MFelix · Accepted solution]

Hi @Anonymous ,

This calculation altough refers to the total table is based on the values of the selection meaning that any filters will impact this calculation making a selection of lines in a table is applying a filter to the card.

If this metric is only used on this card and you don't want it to change no matter what filters you apply rewrite to:

NbNumberInDoubloon = COUNTROWS(ALL('Query A'))

If you want the metric to have impact of the filters expect when you select other visualizations then you must turn off the interaction between the table and the card:

https://learn.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=powerbi-desktop

[Anonymous]

It works indeed, thank you.

I thought I could apply the same on my other measure that behaves the same :

NbDisctinctNumberInDoubloon = DISTINCTCOUNT('Query A'[phone number])

But whether I use 

NbDisctinctNumberInDoubloon = DISTINCTCOUNT(ALL('Query A')[phone number])

or

NbDisctinctNumberInDoubloon = DISTINCTCOUNT(ALL('Query A'[phone number]))

It gives me error. It doesn't work on the same base ?

[MFelix]

Hi @Anonymous 

Bing a distinct count you need to use a different context:

NbDisctinctNumberInDoubloon = CALCULATE(DISTINCTCOUNT('Query A'[phone number]),ALL('Query A'[phone number]) )

[Anonymous]

It's aint working either 😞 When I select 1 line, the value displayed is 1)

[Anonymous]

Actually, it is just a typo from you I guess, you left the column name in the second argument.

It works fine like this :

NbDisctinctNumberInDoubloon = CALCULATE(DISTINCTCOUNT('Query A'[phone number]),ALL('Query A'))

 I am surprised though that it didn't raise an error with the column name on it.

[MFelix]

Hi @Anonymous,

The ALL function also works with columns however once again the context is important so if you are using columns from the query a on your table the result can be influenced