Count distinct values of a column without measure

[Jeremy19]

Hello,

I need to have a column with the number of distinct IDs in the whole table. I just have to remove the filter from the "Action" column. I made a measure which allows me to have the good result :

measure = CALCULATE(DISTINCTCOUNT(Table[Id]),REMOVEFILTERS(Table[Action]))

However I have to retrieve it for a calculation in an R visual. I have to create a column and not a measure.

Do you know how I can do?

Thanks

[v-jingzhang]

Hi @Jeremy19 

Does my reply solve your problem? If yes, please accept it as the solution. Thanks!

Regards,
Jing

[amitchandak]

@Jeremy19 , filter and slicer values are not considered by calculated column

you can try a new column like 

DISTINCTCOUNT(Table[Id])

or like

calculate(DISTINCTCOUNT(Table[Id]), Table[Action] = "Something" )

[Jeremy19]

It won't work for my problem. To better explain my table is like this:

IdGame  Action   X       Y

1              pass    100   200

1              shot    950   120

1              pass    800    300

2              pass    400    300

2              pass    500    200

I need to create a graph with an R visual where I need to have the average action count (chosen by a slicer) per idGame. The problem is that if the shots are chosen in the slicer the average will be 1 because there will remain only one GameId while I expect 0.5. That's why I thought that having a new column with the number of idGame without the filter on Action allowed me to have the right value

[v-jingzhang]

Hi @Jeremy19 

If you want to have distinct count in a column, DISTINCTCOUNT('Table (2)'[IdGame]) can get the result. 

If this cannot give you what you want in the R visual, maybe you can calculate the average value in a new column, then display the average in the R visual directly.

Column = 
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Table (2)'[IdGame] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[Action] )
    ),
    DISTINCTCOUNT ( 'Table (2)'[IdGame] )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Jeremy19]

Thank you for that answer. I wasn't specific enough. I also have other columns like the team or the date. I have slicers on several columns but when I select values my distinctcount remains the same. It would have to adapt according to all the slicers except that of "Action". That's why it seems complicated to me with a column and not a measure but I need it for the R visual

[v-jingzhang]

Hi @Jeremy19 

Thank you for explanation, but as Amit has said, "filter and slicer values are not considered by calculated column." This is correct.

In Power BI, calculated columns and calculated tables are populated when they are created. Their data can only be updated when you refresh the whole data model. They look as static in the report. They cannot be changed by user interactions on the report like using slicers or filters. Only measures can be dynamcially changed. 

You could refer to this article to understand the differences between them.

Measure vs Calculated Column: The Mysterious Question? Not! - RADACAD

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Jeremy19]

Thank you, but it is not possible to use a measure in my R visual? So it is not possible to solve my problem?

[v-jingzhang]

Hi @Jeremy19 

Based on my test, it is possible to use a measure in a R visual and filter it with external slicers. I attached a sample file which has a table in R. 

Best Regards,
Jing