cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cs_astron Regular Visitor
Regular Visitor

Create a table with a single row of multiple columns of calculated values via a scripts

Hi All,

 

I am trying to create a table with a single row of multiple colum values that are generated from scripts.

 

I originally created multiple measures in the primary table but displaying each measure in a visual was taking time to display since the calculation for each was done on the fly.

 

I then created a column in the primary table based upon a DAX script and have referenced the column in a visual. The speed has picked up as the calculation is created everytime the data is freshed in the table.

 

I am looking for a way to create a separate table that will contain multiple columns ( 1 for each calculated value) so the value was always availble to be displayed quickly without it having to be calculated each time. The table would be refreshed each day.

 

Is this the best approach to solve speed display issues or should I just use the columns created in the primary table?

 

Here is a sample of a script used to calculate a value in the table I wish to display in a card visual.

 

Col_Percent_Standard_API_Mild_High = IF( Calculate(SUM(Producers[CatalystVolume_m3]),FILTER(Producers,(Producers[Harshness] = "Mild") && (Producers[EthyleneCost] = "High") && (Producers[Licensor_CatalystSupplier] = "Standard/API"))) = 0, 0, Calculate(SUM(Producers[CatalystVolume_m3]),FILTER(Producers,(Producers[Harshness] = "Mild") && (Producers[EthyleneCost] = "High") && (Producers[Licensor_CatalystSupplier] = "Standard/API"))) / Calculate(SUM(Producers[CatalystVolume_m3]),FILTER(Producers,(Producers[Harshness] = "Mild") && (Producers[EthyleneCost] = "High") && (Producers[Licensor_CatalystSupplier] = "Standard/API" || Producers[Licensor_CatalystSupplier] = "Standard/non-API" )))  )

 

 

 

Any help would be appreciated...

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
cs_astron Regular Visitor
Regular Visitor

Re: Create a table with a single row of multiple columns of calculated values via a scripts

Thanks Frank for the suggestion.

 

I managed to find my own solution. Here is my solution. I had to display 48 different calculated measures in separate visuals. My first attempt was to create all of the measures in my primary table. When I would run the reports (visuals on the dashboard), it took a little bit to display all 48. This was not going to sit well with the client if they had to wait for all of calculation to generate to display the visuals.

 

I tried to create a separate table to hold all of my measures. That way, I would be able to reference the column for the visual and the "on the fly" calculation process would be eliminated. My mistake was to create the table and try to add a custom column through the Power Query editor. When I added my script that had several filters in a IF statement, the process did not like the && symbol.

 

The solution for me was to create the table in the "Relationship" section with one column callled "Type" and and entered the value "Results". Then, I went back to the "Report" section. On the right hand side, the tables and fields are displayed. In my new table, I added a new column with the script that would generate my desired value. This script was written to reference values in the primary table. 

 

I added some new visuals (card, pie chart, donut chart or gauge) and referenced the new columns from my new table. This process solved my speed issue as the visuals displayed immediately instead of waiting for the measures to calcuate the results and display them each time the user displays that page of visuals.

 

Table_Creation.PNGVisuals_Using_Separate Table.PNGVisual Results

3 REPLIES 3
cs_astron Regular Visitor
Regular Visitor

Creating a table of measures from the primary table

Hi Everyone,

I am trying to build a table with calculated measures (i.e. one row of multiple colums that each column contains the result of a calculated measure. I am having an issue in creating it. I must be missing something in the process. I created a table and I then try to created a custom column and insert the DAX script to generate the value but it has an issue with the script. I seems it does not like the AND symbol (&&) in the script. I am at a lost.

 

I am trying to create this table so the values are updated when the data is refreshed daily instead of creating a measure in the original table. When using the measure from the original table in a visual ( I have 48), It takes some time to display the dashboard as the values are being calculated on the fly thus slowing down the display of the visuals.

 

Here is a sample of the script:  EO_Producers is the primary table

 

Percent_Standard_API_Mild_High = IF( Calculate(SUM(EO_Producers[CatalystVolume_m3]),FILTER(EO_Producers,(EO_Producers[Harshness] = "Mild") && (EO_Producers[EthyleneCost] = "High") && (EO_Producers[Licensor_CatalystSupplier] = "Standard/API"))) = 0, 0, Calculate(SUM(EO_Producers[CatalystVolume_m3]),FILTER(EO_Producers,(EO_Producers[Harshness] = "Mild") && (EO_Producers[EthyleneCost] = "High") && (EO_Producers[Licensor_CatalystSupplier] = "Standard/API"))) / Calculate(SUM(EO_Producers[CatalystVolume_m3]),FILTER(EO_Producers,(EO_Producers[Harshness] = "Mild") && (EO_Producers[EthyleneCost] = "High") && (EO_Producers[Licensor_CatalystSupplier] = "Standard/API" || EO_Producers[Licensor_CatalystSupplier] = "Standard/non-API" )))  )

 

What are the correct steps to create this table or is it better to create a column in the primary table based upon the script?

 

I need a way to display these calculated values that does not slow up the display of the dashboard. Any help would be appreciated.

 

Thanks

Community Support Team
Community Support Team

Re: Create a table with a single row of multiple columns of calculated values via a scripts

Hi @cs_astron,

 

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
cs_astron Regular Visitor
Regular Visitor

Re: Create a table with a single row of multiple columns of calculated values via a scripts

Thanks Frank for the suggestion.

 

I managed to find my own solution. Here is my solution. I had to display 48 different calculated measures in separate visuals. My first attempt was to create all of the measures in my primary table. When I would run the reports (visuals on the dashboard), it took a little bit to display all 48. This was not going to sit well with the client if they had to wait for all of calculation to generate to display the visuals.

 

I tried to create a separate table to hold all of my measures. That way, I would be able to reference the column for the visual and the "on the fly" calculation process would be eliminated. My mistake was to create the table and try to add a custom column through the Power Query editor. When I added my script that had several filters in a IF statement, the process did not like the && symbol.

 

The solution for me was to create the table in the "Relationship" section with one column callled "Type" and and entered the value "Results". Then, I went back to the "Report" section. On the right hand side, the tables and fields are displayed. In my new table, I added a new column with the script that would generate my desired value. This script was written to reference values in the primary table. 

 

I added some new visuals (card, pie chart, donut chart or gauge) and referenced the new columns from my new table. This process solved my speed issue as the visuals displayed immediately instead of waiting for the measures to calcuate the results and display them each time the user displays that page of visuals.

 

Table_Creation.PNGVisuals_Using_Separate Table.PNGVisual Results