creating Measures for treating data from Azure

[Anonymous]

Hello

I am retrieving data from Azure through "Analysis services" live connection. 

I think that the only way I have to treat the data for the reprots is by creating measures. 

When it comes to create measures to filter more the information I think I shuld use SUMMARIZE fucntion. 

I have a table called Table1 with (Type, Code, Category) and when I try to craete  simple measures to display it says "cant display":

Measure = SUMMARIZE(Table1, Table1[Type], "EC", ALL(Table1[Code]) )

Measure2 = SUMMARIZE(Table1, Table1[Type], "EC", Table1[Code] = "1234")

Could you please help me to understand what is goign on? 

I am not really familiar with SUMMARIZE expressions since so far I have been working with imported data.

Thanks for tips and advices

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Code_measure =
IF(MAX('Table'[Code])=1234,SUM('Table'[Code]),BLANK())

2. Result:

Does this meet your expected results?

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft]

Hi  @Anonymous ,

The function Summrize returns a table value, but whether you are using a measure or a calculated column, only a single value will be displayed. Therefore, assigning the table value to the measure will report a related error." The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

So if you use measure, you will only return a single value, such as count and sum. If you want to get a table, you need to use calculated table.

I created some data:

Use calculated table to achieve your requirements:

Table 2 =
SUMMARIZE(FILTER('Table','Table'[Code]=1234),'Table'[Type],"EC",SUM('Table'[Code]))

Result：

Use Measure:

1. If you input the function in the calculated table into the measure, it will report the same error as yours, because meausre outputs a value

2. We can judge by measure, if the condition is met, it is 1, otherwise it is 0

Measure =
IF(MAX('Table'[Code])=1234,1,0)

3. Put measure into Filter, set is=1, apply filter.

4. Result：

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

thanks for your support. Your explanation is quite clear. 

So if we use the data from a model from the cloud, as I am using, it is not posible to create new tables. I can only create measures. Therefore I think I am quite limitated in qhat respects to creating reports with more calculations. For example if I want to filter eventcode=1234 previosly and then create a visualization with a measure calculation based on the filtered table. Is there a possibility to do so? 

I am afraid that with live connection mode I only can create visualizations based on the data of the tables coming from the cloud and then filter them by this field (which also does not allow me to filter with 3 or 4 event codes.

Is this the only way?  After this step I need to do more calculations to get the value that I want to display. Is there a way to do everything with measures? Because in "live connection" mode a I dont see it.

Thanks 

[selimovd]

Hey @Anonymous ,

the SUMMARIZE function returns a table. So if you say MyMeasure = SUMMARIZE(...) that doesn't make sense as a measure has to be scalar and only return a number.

You can use Summarize to build a temporary table and then use another function to get a scalar result from that table. I don't know if it makes sense, but the following could work:

MyMeasure =
VAR vSumTable = SUMMARIZE(Table1, Table1[Type], "EC", ALL(Table1[Code]) )
VAR vResult = SUMX( vSumTable, [EC] )
RETURN
    vResult

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hello,

It is still not workign in that way. 

I try to change from card to table visualizations and nothing displayed...

I did exactly what you suggested. On another hand for more information I have created the measure inside the Table1. 

On another hand I would like to know how could I troubleshoot problems when I am not improting data. I am used to improt data and create tables (checkt them before creatign reports, etc). In this way it is hard to troubleshoot.