Apply a measure on each row of a heavy table

[Anonymous]

Hi everyone,

I'm facing a problem and I haven't found any solution on the forum so far, so I decided to ask for your help.

Here is the problem :

I have a table with many rows (50'000, but a visual filter context is already applied and bring it down to 1500). One of the column is a "opening" date, and I need to tag and / or filter out the rows that are opened since a DYNAMIC amount of time, based on user input (a slicer).

I created a calculated column with the number of days between the opening date and "now", I get the user input from a slicer (Warning_Time_Frame) and I use a simple Measure that can be used also at the row level :

Highlight Warning Rule = 
IF(AVERAGEA(table[NumDaySinceOpening]) > [Warning_Time_Frame],1,0))

 
I could easily use this measure as a conditional formatting rule and highlight the desired rows as wished.
However how can I now filter the highlighted rows if I want to ?

Adding a visual filter on the table based on 'Highlight Warning Rule' seems to be too heavy to be computed as the visual never updates. Is it really that heavy to do a simple measure like this one a thousand times ?

My questions are :
1) I'm interested to know, why is it possible to compute this filtering on the conditional formatting but not to filter the table ? I would expect that the conditional formatting needs to apply internally excatly the same process.
2) What would be a turn around to filter out the highlighted rows efficiently ?

Note : 
- I can't create a calculated column as I need the filtering to be updated with the user UI input
- I am aware of TOPN filter for heavy tables, but here without the measure the processing time is very ok

Thanks in advance for your precious help.

[v-kkf-msft]

Hi @Anonymous ,

Don't clear your model, I can use the measure to filter rows like this:

Could you please share your PBIX file without sensitive data? That will help us solve problem more quickly.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Greg_Deckler , @v-kkf-msft and @justivan ,

Thanks to your help and your insights, I figured out what the problem was. It was related with the data model relationships rather than the measure itselfs.
However I still don't understand why the behavior is so, and I would be delighted if one of you can explain to me why it is so, and how I could change the measure in order to not need the "trick" I found ? 

Super easy mock example :

I have two tables, one Fact and one dimension :

There are the model relationships :

 
A visual table considering only fact's fields and the measure works perfectly, as adding a dimensions field without the measure.
However, adding both the measure and a field from dimension table makes Power BI wanting to consider all possible combinations and loose the fact - dim relationship : 

 To fix this, I added a two-way relationship between fact and dim and simply summarize the dimension field by "First" :

Could you please explain to me why adding this measure to the table makes Power BI loose the relationship ? What is it that I didn't understand about filtering context ?
I understand that slicing from the fact table would require a double-way relationship, but here I'm only computing a measure for every rows of the fact table.

How could I update the measure to make it automatically do what I want without the need of summarize the dimension field (which make no sense to do so) ?
Actual measure :

Highlight Rule = IF(AVERAGE(Fact_TestTable[Fact_Value])>Warning_Time_Frame[Valeur Warning_Time_Frame],1,0)

Here is the pbix file of this simple example : https://we.tl/t-KBJKBI5Nlc

Thanks again for your time and sorry I didn't target where the real problem was.
Cheers

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I'm using the Performance Analyzer in Power BI to get the DAX.

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Warning_Time_Frame'[Warning_Time_Frame])),
      'Warning_Time_Frame'[Warning_Time_Frame] = 101
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Fact_TestTable'[Fact_Name],
      'Fact_TestTable'[Dim_id],
      'Dim_TestTable'[Dim_Name],
      __DS0FilterTable,
      "SumFact_Value", CALCULATE(SUM('Fact_TestTable'[Fact_Value])),
      "Highlight_Rule", 'Fact_TestTable'[Highlight Rule]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      501,
      __DS0Core,
      'Fact_TestTable'[Fact_Name],
      1,
      'Fact_TestTable'[Dim_id],
      1,
      'Dim_TestTable'[Dim_Name],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Fact_TestTable'[Fact_Name], 'Fact_TestTable'[Dim_id], 'Dim_TestTable'[Dim_Name]

It rans:

SUMMARIZECOLUMNS(
'Fact_TestTable'[Fact_Name],
'Fact_TestTable'[Dim_id],
'Dim_TestTable'[Dim_Name] )

 So, this was effectively a cross join between Fact_TestTable and Dim_TestTable with a filter that removes rows from the result where [Highlight Rule] returned BLANK. However the definition of [Highlight Rule] would never return BLANK because its result is 0 or 1. Because [Highlight Rule] would never return BLANK it does not remove rows from the result so the extra rows produced by the cross join remain. So you need use:

Highlight Rule = 
IF(AVERAGE(Fact_TestTable[Fact_Value])>Warning_Time_Frame[Valeur Warning_Time_Frame],1)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is exactly the answer I was looking for, thanks a lot 🙂

[justivan]

Hi @Anonymous ,

Have you tried something like this? It's hard without seeing your model ( at least for my level 😂) but maybe you could give this DAX a try.

EVALUATE
VAR __Test =
    SUMMARIZE (
        ADDCOLUMNS (
            'Sales',
            "@NumDaySinceOpening", DATEDIFF ( Sales[Order Date], Sales[Due Date], DAY )
        ),
        [@NumDaySinceOpening],
        "@Sales", [Sales Amount]
    )
RETURN
    FILTER ( __Test, [@NumDaySinceOpening] > 8 )

Edit: I played around on https://dax.do/ and I think you could apply the same technique to get your result.

[Greg_Deckler]

@Anonymous Just use your measure in the Filters pane to filter out the rows based on the value of 1 or 0. You created what I call a Complex Selector: The Complex Selector - Microsoft Power BI Community

[Anonymous]

Hi @Greg_Deckler , thx a lot for your quick answer, really appreciated 🙂
Yes, I tried this but as mentioned, it (suprinsingly because it works smoothly with conditional formatting) takes ages to process and in the end never it updates the visual.
Can the table just be too complex ? But why would it work smoothly with the conditional formatting then 🤔
If you are aware of any other possibilities, let me know. Cheers.

[Greg_Deckler]

@Anonymous Yep, Greg gets bad marks for apparently not reading the entire post. Performance issues with DAX are tricky. I don't suppose you can share the PBIX? Let me understand fully what is going on. I am thinking the following is truy:

• You have a table visualization
• In this table visualization you are returning a measure: AVERAGEA(table[NumDaySinceOpening])
• This all works fine with the conditional highlighting measure you specified but when you use it in a visual filter it chokes

Let me know if all of that is true.

[Anonymous]

Thx for answering back @Greg_Deckler ! 🙂

Yeh what you described is right, with a bit more details :

-- A table visualisation based on different dataset linked through a datamodel is used. A lot of rows are present (50k+) but only 1k+ are displayed (different visual filters).

-- The following measure :

Highlight Warning Rule = 
IF(AVERAGEA(table[NumDaySinceOpening]) > [Warning_Time_Frame],1,0))

is based on one column of the table, and an other measure that take user inputs from a slicer.

-- Using this measure with conditional formatting works well ; it highlighted the rows where the measure output 1, the results is coherent and its super rapid to compute.

-- When the same measure is added to the filter pane, it chokes and never updates

Note : I can't show for instance the measure in the table, it will also process for ever

[Greg_Deckler]

@Anonymous That is really odd that it works in conditional formatting but not in the table itself. That's strange. I am going to have to do some tests around that. I honestly cannot tell you why that would be the case. Now, just because 1000 rows are in the table (summarized), how many rows do you think are in context, all 50K rows? Even so, usually that shouldn't be a problem for DAX. Do you have to use AVERAGEA or can you use AVERAGE? Not sure if that would help or not.

If I were to mock this up would it be something like:

Table

[Store], [Date], [NumDaySinceOpening]

Then aggregate on Store for the table visualization.

[Anonymous]

@Greg_Deckler I brought down the data I import to 1K rows in total, to decipher if the size of the dataset is the bottleneck.
I have exactly the same behavior, so it should come from someting else. I have 1k rows in the table (no summarization, no aggregation, only every element), and then the measure that in that context should be applied to every row. 
again it works well in the conditional formatting but not with the filter pane or if I try to add it to the table.

Thanks for your help so far Greg, I will investigate that in more details and come back here when I'll figure out a solution.

[Greg_Deckler]

@Anonymous I mocked this up in a dataset. Table3 in the attached PBIX below sig. Couldn't really recreate the issue so there is something about your data or model that is causing havoc.