Forum Discussion

BillyT_350's avatar
BillyT_350
Helper V
8 years ago
Solved

Slow Measures

I have report that analyzes data on customers. Data is filtered by three slicers, one for customer, another for data date, and the third for internal business unit (responsible for the customer).   ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    With this measure, you are calling, and calculating the [Scr%_ApptDesir] measure 7 times...it only needs to be calculated once.

     

    try this:

     

    Grd_ApptDesir =
VAR Score = [Scr%_ApptDesir]
RETURN
    SWITCH ( TRUE (),
        Score >= .9, "A",
        Score >= .8, "B",
        Score >= .7, "C",
        Score >= .6, "D",
        "F"
    )
jscottNRG's avatar
jscottNRG
Helper II
7 years ago

Based on this discussion, I think my DAX code would benefit from optimization.  My report takes up to a minute to load visuals after making a slicer selection, and it makes the report unusable.  I'd appreciate any suggestions to improve my work!

 

I have a central fact table, Jobs, and I'm trying to add columns with data from other related fact tables (using the ID field).  Item1, Item2, etc. data are spread across a handful of tables and I'm trying to get counts for Fail and Pass for all Items all in one place:

 

Results = ADDCOLUMNS(Jobs,
"Item1_PASS", COUNTAX ( FILTER ('DataTable1', 'DataTable1'[Item1] = "Pass" && 'DataTable1'[ID]=Jobs[ID]), 'DataTable1'[Item1]),
"Item1_FAIL", COUNTAX ( FILTER ('DataTable1', 'DataTable1'[Item1] = "Fail" && 'DataTable1'[ID]=Jobs[ID]), 'DataTable1'[Item1]),

 

My understanding was that I need to create measures (in a separate measure table) that calculate a sum for each of the columns that I'm adding to Jobs:

 

Item1_FAIL measure:

Item1_FAIL = SUM('Results'[Item1_FAIL])

The end goal is a visual with counts of Pass and Fail for each Item that will work with slicers that use other columns from Jobs, like this:

 

visual settingsvisual example

 

Fail Count = 
SWITCH (
    FIRSTNONBLANK( 'Item_key'[Item Name], 1),
"Full description for Item1.", 'My Measures'[Item1_FAIL],

Again, this report has become very frustrating to use because of the load times - I have over 200 Items to display results for.  I imagine I could set up my code in a better way, but I'm not sure what that would be.  Thanks in advance for suggestions, and please let me know where I can clarify anything.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

jscottNRG, please post this response in a new thread, and mention me in it.

 

I think it's best practice to keep a thread to a single issue.  Measure Optimization is a broad topic, but this deserves its own thread.

 

Also, please include a picture of your relationships so that I can see the other dimension tables as well.

 

Thanks,

 

~ChrisHaas