showing distinct values of calculated measure

[skowronp]

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

[v-huizhn-msft]

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

[skowronp]

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.

[skowronp]

I added simple sample to show exactly where is the problem.

https://wetransfer.com/downloads/242a973afed35814dc0866ab9b085aa220171123124553/1fe9e254f54452d67a44...

[v-huizhn-msft]

Hi @skowronp,

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

Thanks,
Angelia

[skowronp]

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

[v-huizhn-msft]

Hi @skowronp,

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

Best Regards,
Angelia