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.