Color value

Hello,
a have i model

and mesure
ODM Količina po odpoklicu:=
VAR zaloga =
SUMX(
SUMMARIZE(
'f ODM Odpoklici_MES',
'f ODM Odpoklici_MES'[ODM Odpoklic_ID],
'd CAS Cas'[CAS Datum],
"Enkratna_Količina", MAX('f ODM Odpoklici_MES'[ODM Kolicina_po_odpoklicu])
),
[Enkratna_Količina] )
RETURN
zaloga

ODM Zaloga končnih kosov:=
VAR temp =
ADDCOLUMNS(
SUMMARIZE('f ODM Odpoklici_MES', 'f ODM Odpoklici_MES'[ODM Odpoklic_ID]),
"DistinctValue", MAX('f ODM Odpoklici_MES'[ODM Zaloga_koncnih_kosov])
)
RETURN
SUMX(temp,[DistinctValue])

ODM Količina kumulativno po odpoklicu:=
VAR CurrentID = SELECTEDVALUE('f ODM Odpoklici_MES'[ODM Odpoklic_ID])
VAR CurrentDate = MAX('f ODM Odpoklici_MES'[ODM Datum_odpoklica])

RETURN
CALCULATE(
SUMX(
SUMMARIZE(
'f ODM Odpoklici_MES',
'f ODM Odpoklici_MES'[ODM Odpoklic_ID],
'd CAS Cas'[CAS Datum],
"Enkratna_Količina", MAX('f ODM Odpoklici_MES'[ODM Kolicina_po_odpoklicu])
),
[Enkratna_Količina]
),
FILTER(
ALLSELECTED('f ODM Odpoklici_MES'),
'f ODM Odpoklici_MES'[ODM Odpoklic_ID] = CurrentID &&
'f ODM Odpoklici_MES'[ODM Datum_odpoklica] <= CurrentDate
)
)

ODM Zaloga mehansko obdelanih kosov:=
VAR temp =
ADDCOLUMNS(
SUMMARIZE('f ODM Odpoklici_MES', 'f ODM Odpoklici_MES'[ODM Odpoklic_ID]),
"DistinctValue",
IF(
ISBLANK(MAX('f ODM Odpoklici_MES'[ODM Zaloga_mehansko_obdelanih_kosov])),
0,
MAX('f ODM Odpoklici_MES'[ODM Zaloga_mehansko_obdelanih_kosov])
)
)

RETURN
SUMX(temp, [DistinctValue])

ODM Barvanje Base:=
VAR ManjkoKoncni = [ODM Količina kumulativno po odpoklicu] - [ODM Zaloga končnih kosov]
VAR ManjkoPoMehanski = ManjkoKoncni - [ODM Zaloga mehansko obdelanih kosov]

RETURN
SWITCH(
TRUE(),
ManjkoKoncni <= 0, 1, -- ✅ Zelena
ManjkoKoncni > 0 && ManjkoPoMehanski <= 0, 2, -- 🟠 Oranžna
ManjkoPoMehanski > 0, 3, -- ⚪ Bela
BLANK()
)

ODM Barvanje Trajno1:=
VAR CurrentDate =
MAX('d CAS Cas'[CAS Datum])

VAR VsiDatumiInStanja =
FILTER(
ADDCOLUMNS(
ALL('d CAS Cas'),
"Stanje", [ODM Barvanje Base]
),
'd CAS Cas'[CAS Datum] <= CurrentDate &&
NOT(ISBLANK([ODM Barvanje Base]))
)

VAR ZadnjeZnanoStanje =
MAXX(VsiDatumiInStanja, [Stanje])

RETURN
ZadnjeZnanoStanje

----------------------------------
When I create the visual in Power BI RS and apply conditional formatting based on the recall quantity values, the rendering takes 10 minutes or more to complete.
PLS Help

Need Help

LeaRupnik
1 year ago
We resolved the issue, but not in the way you suggested. Thank you.

14 Replies

Replies have been turned off for this discussion

SolomonovAnton
Super User
1 year ago

🔍 Performance Bottleneck Analysis
Repeated use of SUMMARIZE + MAX + SUMX: Very costly with large datasets.
ALLSELECTED inside complex FILTER: Can slow down visuals drastically with slicers.
Chained Measures: Each reference adds evaluation overhead.
✅ Optimized Strategy
Step 1: Cache with Variables
DAX ODM Količina kumulativno po odpoklicu := VAR CurrentID = SELECTEDVALUE('f ODM Odpoklici_MES'[ODM Odpoklic_ID]) VAR CurrentDate = MAX('f ODM Odpoklici_MES'[ODM Datum_odpoklica]) VAR BaseTable = ADDCOLUMNS( FILTER( 'f ODM Odpoklici_MES', 'f ODM Odpoklici_MES'[ODM Odpoklic_ID] = CurrentID && 'f ODM Odpoklici_MES'[ODM Datum_odpoklica] <= CurrentDate ), "Enkratna_Kolicina", 'f ODM Odpoklici_MES'[ODM Kolicina_po_odpoklicu] ) RETURN SUMX(BaseTable, [Enkratna_Kolicina])
Step 2: Use TOPN for Last Known State
DAX ODM Barvanje Trajno1 := VAR CurrentDate = MAX('d CAS Cas'[CAS Datum]) VAR FilteredTable = CALCULATETABLE( ADDCOLUMNS( VALUES('d CAS Cas'[CAS Datum]), "Stanje", [ODM Barvanje Base] ), 'd CAS Cas'[CAS Datum] <= CurrentDate, NOT ISBLANK([ODM Barvanje Base]) ) VAR LatestState = TOPN(1, FilteredTable, 'd CAS Cas'[CAS Datum], DESC) RETURN MAXX(LatestState, [Stanje])
Step 3: Profile Measures with DAX Studio
Open your PBIX in DAX Studio.
Enable Server Timings and Query Plan.
Identify high-cardinality steps and expensive scans.
Step 4: Pre-Aggregate Data
DAX SummaryTable = SUMMARIZE( 'f ODM Odpoklici_MES', 'f ODM Odpoklici_MES'[ODM Odpoklic_ID], "MaxKolicina", MAX('f ODM Odpoklici_MES'[ODM Kolicina_po_odpoklicu]), "ZalogaKoncnih", MAX('f ODM Odpoklici_MES'[ODM Zaloga_koncnih_kosov]) )
📌 Next Steps
Test one optimized measure in a visual.
Use Performance Analyzer in Power BI to confirm speedup.
Share row count of f ODM Odpoklici_MES and number of visuals on page if issue persists.
- LeaRupnik
  Helper III
  1 year ago
  I am attaching a test Power BI file with data sourced from Excel. How cen i atach it??
  https://drive.google.com/file/d/1MSLK-mPlkcooboyqwuu2WHZDFcg4mbpl/view?usp=sharing
  My goal is to continue the color formatting with the same color even if there is no data for a specific date — the color should remain consistent until a change in the value triggers a new color, and so on.
  I would appreciate your help with achieving this.
  - Anonymous
    Not applicable
    1 year ago
    Hello LeaRupnik ,
    
    Thank you for reaching out to Microsoft Fabric Community Forum.
    
    SolomonovAnton Thank you for your quick response.
    
    LeaRupnik Thanks for clarifying your goal!
    
    You're absolutely right in wanting the color to persist across dates even if there’s no data for that specific day , and only update when the value changes.
    
    As SolomonovAnton already pointed out in Step 2, the measure using TOPN to get the last known non-blank state is exactly what you need for this:
    
    This approach ensures that:
    
    Even if there’s no [ODM Barvanje Base] value for today’s date,
    
    The visual will use the most recent past value for coloring,
    
    Creating a "carry-forward" effect until a new status appears.
    
    Just make sure:
    
    Your date dimension has a full continuous range of dates (no missing days).
    
    You use this [ODM Barvanje Trajno1] measure for conditional formatting in your visuals.
    
    If you're still facing slow rendering, try minimizing the number of visuals that reference this measure, and check performance using DAX Studio or Performance Analyzer.
    
    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
    
    Regards,
    
    B Manikanteswara Reddy
Anonymous
Not applicable
1 year ago
Hi LeaRupnik ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy
- Anonymous
  Not applicable
  1 year ago
  Hi LeaRupnik ,
  
  May I ask if you have gotten this issue resolved?
  
  If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
  
  Regards,
  
  B Manikanteswara Reddy
LeaRupnik
Helper III
1 year ago
Hello,
The solution still doesn’t work — it keeps processing endlessly and eventually returns an error.
- Anonymous
  Not applicable
  1 year ago
  Hi LeaRupnik ,
  
  Thanks for the update, and sorry to hear the issue is still ongoing.
  
  Since the report keeps processing indefinitely and eventually fails, this suggests there may be a more serious performance bottleneck or memory-related issue during calculation.
  
  Here are a few workarounds that can help:
  
  1.Try breaking down the [ODM Barvanje Base] logic and testing each part in a simple visual — ideally filtered to a small date range or a single ODM Odpoklic_ID. This can help identify which part is causing delays or failure.
  
  2.Ensure the summary table is correctly implemented to avoid complex on-the-fly calculations:
  
  SummaryTable =
  SUMMARIZE(
      'f ODM Odpoklici_MES',
      'f ODM Odpoklici_MES'[ODM Odpoklic_ID],
      'f ODM Odpoklici_MES'[ODM Datum_odpoklica],
      "Kolicina", MAX('f ODM Odpoklici_MES'[ODM Kolicina_po_odpoklicu]),
      "ZalogaKoncni", MAX('f ODM Odpoklici_MES'[ODM Zaloga_koncnih_kosov]),
      "ZalogaMehanska", MAX('f ODM Odpoklici_MES'[ODM Zaloga_mehansko_obdelanih_kosov])
  )
  
  You can then use this table in your measures instead of querying the full fact table. This significantly improves performance.
  
  3.If you’re not familiar with DAX Studio, you can use Power BI’s built-in Performance Analyzer:
  
  Go to View > Performance Analyzer
  
  Start recording, then refresh the visuals
  
  Identify which visuals/measures are the slowest
  
  You can click Copy Query and share it here if needed
  
  As highlighted earlier by super user, the TOPN logic is the correct way to retain the last known colour state when no data is available for a specific date. Just ensure:
  
  Your date table is marked as a proper Date Table and contains a continuous range of dates.
  
  You can use [ODM Barvanje Trajno1] for conditional formatting in visuals.
  
  If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
  
  Regards,
  
  B Manikanteswara Reddy
LeaRupnik
Helper III
1 year ago
We resolved the issue, but not in the way you suggested. Thank you.
- Anonymous
  Not applicable
  1 year ago
  Hi LeaRupnik ,
  
  Glad to hear that your issue has been resolved!
  
  If possible, it would be great if you could also share the steps or solution that helped you fix it. This would be really valuable for others who might face the same issue in the future.
  
  Thanks in advance for sharing your experience!
  
  Please don't forget to give a "Kudos " – I’d truly appreciate it!
  
  Regards,
  
  B Manikanteswara Reddy

Forum Discussion

Color value

14 Replies

🔍 Performance Bottleneck Analysis

✅ Optimized Strategy

Step 1: Cache with Variables

Step 2: Use TOPN for Last Known State

Step 3: Profile Measures with DAX Studio

Step 4: Pre-Aggregate Data

📌 Next Steps

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Related content

Field value color errors.

Change Slicer Values Color (Border)

Coloring Legend Category Values

Color in Dax

Create colors based on value, not category