Min function for measure in Power BI

[Analitika]

Hi,

How write minimum function min for measure? min(dm) error.

[Greg_Deckler]

@Analitika This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Analitika]

I have a column date.

measure=selectvalue(date)

min(measure) getting error.

[Greg_Deckler]

@Analitika There is no reason to get a MIN of a measure in your case. Measures return a single value. Thus, min, max, sum, average, it's all the same. Just reference the measure directly:

[measure]

Nothing around it.

If you need to get a min of a measure in a table context, then see previous message. But, you have to return a table with the measure calculated at each row and then use MINX across that table to get the minimum of the measure within that table context.

[Analitika]

The problem is whenever I try add date column in visual it makes resources exceeded and table isn't being viewed.

[Greg_Deckler]

@Analitika It's hard to say exactly where your issue is. But I don't think it is something like that measure. I feel like I have about 1/6th of the story here. What visual are you using? Are you using a date table? How many fact rows do you have? What else is in the visual? Can you use the Filter pane to limit which dates the visual is displaying?

It's not a typical situation where a table visual exceeds resources so there must be more to this.

[Analitika]

I have some columns with calculations. I wonder if I can optimize this measure:

Filtered measure_t2 = 
SUMX (
    FILTER (
        VALUES ( 'N08-Klientai'[N08_PAV]),
        [20_2_1_DATA PRADELSIMAS] >= 61 && [20_2_1_DATA PRADELSIMAS]<=90
    ),
    CALCULATE ( [18_1_0_BENDRA SKOLA (nuo istorijos pradzios)] )
)

Because table consumes many resources.

[Greg_Deckler]

@Analitika Filtering between a range can cause performance issues. Is the [20_2_1_DATA PRADELSIMAS] a measure or column? Not sure you need the CALCULATE statement, should perhaps just be able to reference the measure directly. Depends. Any chance you can share the PBIX?

I did a post on optimizing DAX in a similar situation: Performance Tuning DAX - Part 1 - Microsoft Fabric Community

[Analitika]

It is a measure. No I can't share pbix.

[Greg_Deckler]

@Analitika OK, for any measures in the formula you posted, can you post those measure formulas as well?

[Analitika]

20_2_1_DATA PRADELSIMAS = 
VAR SlicerDate =
    MAX ( 'Date'[Date] )
VAR pradzia =
    CALCULATE (
        MIN ( I44_SKOL[I44_DATA_MOK]),
        FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= SlicerDate )
    )
VAR pradelstos_dienos =
    DATEDIFF ( pradzia, SlicerDate, DAY )
RETURN
    IF (
        ABS ( [18_1_0_BENDRA SKOLA (nuo istorijos pradzios)] ) > 0.01,
        CALCULATE ( pradelstos_dienos, FILTER ( 'Date', 'Date'[Date] <= SlicerDate ) )
    )

[Greg_Deckler]

@Analitika Oh boy, I can see where this is perhaps creating some issues. Without changing too much, see if something like this helps:

Filtered measure_t2 = 
VAR __Table = 
  ADDCOLUMNS(
    DISTINCT( 'N08-Klientai'[N08_PAV] ),
    "__Measure", [20_2_1_DATA PRADELSIMAS]
  )
RETURN
SUMX (
    FILTER (
        __Table,
        [__Measure] >= 61 && [__Measure]<=90
    ),
    CALCULATE ( [18_1_0_BENDRA SKOLA (nuo istorijos pradzios)] )
)

What is [18_1_0_BENDRA SKOLA (nuo istorijos pradzios)]. If it is a measure, what is the formula?

[Analitika]

measure of debt