Forum Discussion

LeaRupnik's avatar
LeaRupnik
Helper III
1 year ago
Solved

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], "E...
Need Help
  • LeaRupnik's avatar
    LeaRupnik
    1 year ago

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

SolomonovAnton's avatar
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's avatar
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's avatar
    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

    • LeaRupnik's avatar
      LeaRupnik
      Helper III
      1 year ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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:

         

        1. 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.
        1. 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:

        1. Go to the View tab in Power BI Desktop.
        2. Click Performance Analyzer.
        3. Press Start Recording, then refresh your visual.
        4. 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