cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

__________

100

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.

cheers,

P

6 REPLIES 6
Microsoft Employee

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,
Angelia

Frequent Visitor

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.

Frequent Visitor
Microsoft Employee

Hi @skowronp,

Thanks,
Angelia

Frequent Visitor

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

Microsoft Employee

Hi @skowronp,

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

Best Regards,
Angelia

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.