Forum Discussion

Sabri-All's avatar
Sabri-All
Regular Visitor
4 years ago
Solved

User filter doesn't apply on distinctcount

Hello, 

 

I have two tables

  • Orders (column: ID, accountId, date, type)
  • Accounts (column: accountId, Name)

The purpose is to get for every account the number of unique order types. For example, if an account have 5 orders (2 of type A, 1 of type B and one of type C), I should get 3 as number. For that I used this measure:

CALCULATE(DISTINCTCOUNT(Orders[Type]).
 
It works fine, but when I add a visual filter on date, and I try to change the dates of orders, I don't get the correct values. Exemple:
  • AccountX | 01/01/2019 | Type A
  • AccountX | 01/01/2020 | Type B
  • AccountX | 01/01/2021 | Type A
Without filter, value is 2. If I filter on 2021, I get also 2. I think it checks if the account has an order in 2021 and if so it shows the value that is already calculated. 
 
How can I manage to make it calculte after the users does the visual filters?
 
Thank you in advance.
distinctcount
Filter
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, I also created some sample data and I can successfully get the correct count value using the DAX formula:

    Count of unique order types =

CALCULATE(DISTINCTCOUNT(Orders[type]),ALLSELECTED(Orders))

    Then I go to check the interaction type between the slicer and the visuals to make sure they are enabled:

     

    And I can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, I also created some sample data and I can successfully get the correct count value using the DAX formula:

    Count of unique order types =

CALCULATE(DISTINCTCOUNT(Orders[type]),ALLSELECTED(Orders))

    Then I go to check the interaction type between the slicer and the visuals to make sure they are enabled:

     

    And I can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.