Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

showing distinct values of calculated measure

Hi Community,


Lets say I have table with 100 products. I created measure that for given product returns values from A-D (string value based on product parameters).


e.g. Measure Value = switch(product_parameter; par1; A; par2; B; par3;C; par4;D; blank())


Product_ID     Measure value

1                     A

2                     B

3                     A

4                     D

5                     C


Now I want to do simple table showing count of products group by Measure Value so something like this (table visual with only those two columns):


Measure Value | count

A           30

B           12

C           50

D           8




But I have to do this in direct query mode and instead of above get one row result: 


B     100


How can I calculate distinct values of calculated measure? Seems simple but I stucked.





Hi @skowronp,

Please try to create a calculated column using the formula below. 

Column_category = switch(product_parameter; par1; A; par2; B; par3;C; par4;D; blank())

Then you can create a table visual, select the column_category, and sum(Table[value]) as value level, you will get expected result.

Best Regards,

This is exactly how it is done ;(


When I put all products and calculated measure on a table visual the measure is calculated for all products corectly. But If I put only calculated measure and count the measure is calculated only for last product item.

Hi @skowronp,

Please create a calculated column rather than measure as what I said, please download the attachment for more information.


Thank You. The only problem is that calculated column doesnt exist in direct quesry mode ;(

Hi @skowronp,

There are some limitations for Direct Query mode. Please create a idea here and Power BI will become better.

Best Regards,

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors