Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Distinct count + Display the right class

Hello there 🙂

I have the next scenario:

- I have one table with multiple columns, but the relevant ones here are:

 

Test          ID

1               1

1               2

1               3

1               4

1               5

2               5

 

Don't worry about the repeated ID because it is NOT the true primary key of the table.

 

- I want to display DISTINCTCOUNT(ID) in terms of TEST, so DISTINCTCOUNT(ID) should be 'value' and TEST should be 'axis' in the graph's settings.

 

- If TEST is 2, I would like the graph to put the recount in that category instead of put it in 1. The final output should be a graphic with 4 values in "1" and 1 value in "2".

 

- The problem is that now I am getting 5 values in "1" as the output.

 

Any ideas of how can I achieve this output? It would not be a problem for me to create a new column in the table or a new metric in the model.

 

Thank you very much in advance,

J

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Re = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Test],
    "Result",
    IF(
        [Test]=1,
        CALCULATE(
            DISTINCTCOUNT('Table'[ID]),
            FILTER(
                ALLEXCEPT('Table','Table'[Test]),
                NOT([ID] in 
                CALCULATETABLE(
                    DISTINCT('Table'[ID]),
                    FILTER(
                        ALL('Table'),
                        [Test]=2
                    )
                )
                )
            )
        ),
        DISTINCTCOUNT('Table'[ID])
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Anonymous 

    I still didn't get why value is 4 for Test1, the distinct count should be 5 values. could you please explain this?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Distinctcount returns 5 values if I display them in a simple columns graphic. That's okay.

      But I wanna display Distinctcounts as per Test, getting four 1's and one 2. The output should be one column counting 4 for "1" and one column counting 1 for "2". 

      The problem is that I am getting only one column counting 5 for "1". I am getting five 1's, that is to say, PowerBI is "rejecting" the TEST value 2 for ID 5, but I would like to reject TEST value 1 for ID 5.

       

      I hope I made myself more clear.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        PS: When I say "column" I am refering to the bar of the bars graphic.

         

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Re = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Test],
    "Result",
    IF(
        [Test]=1,
        CALCULATE(
            DISTINCTCOUNT('Table'[ID]),
            FILTER(
                ALLEXCEPT('Table','Table'[Test]),
                NOT([ID] in 
                CALCULATETABLE(
                    DISTINCT('Table'[ID]),
                    FILTER(
                        ALL('Table'),
                        [Test]=2
                    )
                )
                )
            )
        ),
        DISTINCTCOUNT('Table'[ID])
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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