Distinct count + Display the right class

[Anonymous]

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

[v-alq-msft · Accepted solution]

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.

[ryan_mayu]

@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]

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]

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