topic Re: Color value in Report Server

Color value

LeaRupnik — Thu, 24 Apr 2025 12:22:01 GMT

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

Re: Color value

SolomonovAnton — Thu, 24 Apr 2025 19:05:38 GMT

🔍 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.

Re: Color value

LeaRupnik — Fri, 25 Apr 2025 11:18:49 GMT

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.

Re: Color value

Anonymous — Fri, 25 Apr 2025 10:30:55 GMT

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

Re: Color value

LeaRupnik — Fri, 25 Apr 2025 11:34:54 GMT

I have now added the persistent coloring logic (barvanje trajno) to the example above and integrated it directly into the report.

However, the coloring is not displaying correctly, and the performance is very slow.

Could you please assist?
Unfortunately, I don’t have experience using DAX Studio or Performance Analyzer.

https://drive.google.com/file/d/1MSLK-mPlkcooboyqwuu2WHZDFcg4mbpl/view?usp=sharing

Thank you in advance!

Re: Color value

Anonymous — Wed, 30 Apr 2025 06:49:27 GMT

Hello @LeaRupnik ,

Apologies for the delay in getting back to you.

Thanks for the update! Let’s break this down and fix the two main problems you're having:

Coloring Not Showing Up Correctly

This often happens because of one of these reasons:

The measure [ODM Barvanje Base] is returning blank values, especially for future dates.
Your date table isn’t set up properly or might be missing some dates.

Here’s what you can check and fix:

Make sure your date table is marked as a proper Date Table:
In Power BI, go to your 'd CAS Cas' table → right-click it in the model view → choose "Mark as Date Table" >pick the [CAS Datum] column.
Check that your date table has all dates (no gaps):
It should have a continuous range of dates ,no missing days. If needed, you can create one using Power BI’s CALENDAR() function or CALENDARAUTO() funtion.
Confirm the relationship:
Make sure 'd CAS Cas'[CAS Datum] is connected to 'f ODM Odpoklici_MES'[ODM Datum_odpoklica] in the data model.

The Report is Very Slow

Even if your formulas are optimized, reports can still slow down when:

The measures are used across too many visuals at once.
Or the dataset is very large.

Here’s what you can try:

Option 1: Pre-calculate the data

Instead of calculating everything on the fly, create a smaller summary table ahead of time:

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

Then use this summary table in your [ODM Barvanje Base] measure — it’s much faster this way.

Option 2: Use Performance Analyzer (no coding needed)

If you’re not familiar with DAX Studio, try Power BI’s built-in Performance Analyzer:

Go to the View tab in Power BI Desktop.
Click Performance Analyzer.
Press Start Recording, then refresh your visual.
You’ll see which visuals take the longest , click Copy Query next to a slow one and paste it here or into DAX Studio if needed.

This will help identify what’s slowing things down.

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

Re: Color value

Anonymous — Mon, 05 May 2025 06:28:51 GMT

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

Re: Color value

Anonymous — Thu, 08 May 2025 11:35:35 GMT

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

Re: Color value

LeaRupnik — Thu, 08 May 2025 11:52:58 GMT

Hello,
The solution still doesn’t work — it keeps processing endlessly and eventually returns an error.

Re: Color value

Anonymous — Thu, 08 May 2025 14:58:20 GMT

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:

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

Re: Color value

AjdaG — Fri, 09 May 2025 09:56:17 GMT

Hi!
I'm Lea's coworker, I took over this issue. Unfortunatelly this also doesn't work. I tried limited output data for only 1 buyer, but it is also too much. It says the memory is not enough. (Photo attached). I did try to analyze with Performance Analyzer, but as long as it is working I have no option to Copy Query (it is not clickable) and when it returns error, the Copy Query remains gray, not blue and clickable as the rest of them.

Thank you for your help!

Re: Color value

Anonymous — Mon, 12 May 2025 15:51:57 GMT

Hi @AjdaG ,@LeaRupnik ,

As you've already tried a lot of the right steps. Even with filtering for just one buyer the report runs out of memory, it strongly suggests your model or measures are overloading Power BI's engine.

1.Instead of using heavy measures with CALCULATE, SUMMARIZE, etc., materialize your calculations once in a summary table. Example:

ODM_Summary =
ADDCOLUMNS(
    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])
)

Then try to add calculated columns to the above table for logic like below:

BarvanjeBase =
VAR ManjkoKoncni = [Kolicina] - [ZalogaKoncni]
VAR ManjkoPoMehanski = ManjkoKoncni - [ZalogaMehanska]
RETURN
SWITCH(
    TRUE(),
    ManjkoKoncni <= 0, 1,
    ManjkoKoncni > 0 && ManjkoPoMehanski <= 0, 2,
    ManjkoPoMehanski > 0, 3,
    BLANK()
)

Because of this, Power BI just reads the value from memory.

For the testing purpose , first test with only 1 table/matrix visual with newly created summary table. This will make debugging easier and avoids the crashing of the report again.
3. All your existing measures like [ODM Barvanje Base], [ODM Kolicina kumulativno po odpoklicu], etc., should reference this summary table, not the original fact table

While testing, it is recommended to create a lightweight version of the report using the ODM_Summary table to validate performance improvements before moving the logic into the full production report.

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

Re: Color value

Anonymous — Thu, 15 May 2025 12:48:03 GMT

Hi @LeaRupnik @AjdaG

May I ask if you have gotten this issue resolved?

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

Re: Color value

LeaRupnik — Fri, 16 May 2025 04:29:59 GMT

We resolved the issue, but not in the way you suggested. Thank you.

Re: Color value

Anonymous — Fri, 16 May 2025 07:52:47 GMT

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