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

[cs_astron]

I have created multiple measures in a table to generate a calculated value to be displayed in a card visual. The problem is that when the dashboard displays all 48 of these the generation and display of these cards are slow. I values are being calculated on the fly thus making the display slow.

I want to create a separate table with a single row and a column for each calculation thus having the calculated value ready to be retrieved from the table to be displayed in a card visual. This should cut down the time as the value should already be calculated and I am just retriveing the value to be displayed. I am having a hard time creating this table.

Type         Col_Percent_Standard_API_Mild_High        Col_Percent_Standard_API_Mild_Med      Col_Percent_Standard_API_Mild_Low
Results                                                            0.85                                                            0.39                                                        0.17

Here is a sample of one of the calculations I want a value for in the table. This way, when the table is refreshed the values are re-calculated and is ready for display.

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" )))  )

Is the the best approach to save time of during display of the visuals on the dashboard or should I just create calculated column in the original table?

Any help would be appreciated.

Thanks,

[cs_astron · Accepted solution]

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.

Visual Results

[cs_astron]

Thanks Maggie for your response. I see that running all of the measures to populate individual visuals would slow down the process as they are trolling through the whole table for each calculation.

My question is, how to create a second table with one row and multiple columns that would contain the results of each calculation.

This way the new second table would be updated each time the data is refreshed. 

Once that second table exists I could reference a specific column for a specific card visual. That way, the display process does not have to wait on the calculation process to display the result. It will already exist in the second table.

I am at a lost on how to create such a table especially since it will be reference the original table for the data to obtain percentage from the calculation. When I do try to create a table with a custom field and add in the calculation, it seems that it does not like the double ampersand symbol (&& - and) in the script.

I am trying to figure out how to create that second table or is there another practice that would work better.

This is an example of the card visuals I am trying to use.

MUltiple visuals with calculation for each

[v-juanli-msft]

Hi @cs_astron

When you use DAX function to select each row which meets each condition, it would interate each row of the table.

So it would be slow when you create multiple measures and show them on the card.

I would suggest you to add condition in the Visual level filter for each visual.

Best Regards

Maggie