Measure filter min value per category

[charlineklapu]

Hello all,

I want to have a measre which gives me the possibilty to filter for the min value (columns Days) within one case (column case). Data is used in direct query mode via analysis services. 

Data looks as followed:

Case	Date	Name	Days
10	16.04.2022 00:00	a	2659,21
10	16.04.2022 00:00	b	22,42
20	16.04.2022 00:00	c	67,45
20	16.04.2022 00:00	a	57,29
30	14.04.2022 00:00	a	5572,92
30	14.04.2022 00:00	e	239,08
40	14.04.2022 00:00	d	36,81

The column days is also a measure (Calculate min with filter on a given status). Which will then let me filter on the lowest values and show all connected columns. Up top, you can see that one case number can show up twice, if the case had two groups, therefore the min value of each group (column Name) is shown. I want each case number only once.

What I want is the column flag:

Case	Date	Name	Days	Flag
10	16.04.2022 00:00	a	2659,21	0
10	16.04.2022 00:00	b	22,42	1
20	16.04.2022 00:00	c	67,45	0
20	16.04.2022 00:00	a	57,29	1
30	14.04.2022 00:00	a	5572,92	0
30	14.04.2022 00:00	e	239,08	1
40	14.04.2022 00:00	d	36,81	1

and by filtering there will, one line per case:

Case	Date	Name	Days	Flag
10	16.04.2022 00:00	b	22,42	1
20	16.04.2022 00:00	a	57,29	1
30	14.04.2022 00:00	e	239,08	1
40	14.04.2022 00:00	d	36,81	1

Thank you!

[v-yangliu-msft]

Hi  @charlineklapu ,

Here are the steps you can follow：

1. Create measure.

Flag =
IF(
    [Days_Measure]=MINX(FILTER(ALL('Table'),'Table'[Case]=MAX('Table'[Case])),[Days_Measure]),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[charlineklapu]

Thank you. 

However this solution won't work for me as it just loads for a very long time and then returns the error message: "The reultset of a query to external data source has exceeded the maximum allowed size of 1.000.000 rows." 

Any other ideas? 

[tamerj1 · Accepted solution]

Hi @charlineklapu 
You may try

Flag =
VAR CurrentDate = 
    MAX ( Table[Date] )
VAR MinDaysValue =
    MINX (
        FILTER ( ALL( Table[Case] ), Table[Date] = CurrentDate ) ),
        [Days]
    )
RETURN
    [Days] = MinDaysValue 

Should return TRUE for min value

[charlineklapu]

Thank you for your effort!

However this measure won't work as well ... 

Error message: OLE DB od ODBC erroro: You have reached the maximum allowable memory allocation for your tier. 

Any ideas on how to fix this? Or a different measure? 

Thank you!

[tamerj1]

Hi @charlineklapu 
Try 

Flag =
VAR CurrentDate = 
    MAX ( Table[Date] )
VAR MinDaysValue =
    MINX (
        FILTER ( ALLSELECTED( Table[Case] ), Table[Date] = CurrentDate ) ),
        [Days]
    )
RETURN
    [Days] = MinDaysValue 

[charlineklapu]

Still not not working ... Same error message

[tamerj1]

@charlineklapu 

Try 

Flag =
VAR CurrentDate =
    MAX ( Table[Date] )
VAR MinDaysValue =
    MINX (
        CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Case], Table[Date] ) ),
        Table[Days]
    )
RETURN
    MAX ( Table[Days] ) = MinDaysValue

[charlineklapu]

First of all, thank you for your countinious help. 

But bad news, still not a solution for my problem. 

Now the error message: "The reultset of a query to external data source has exceeded the maximum allowed size of 1.000.000 rows." 

[tamerj1]

@charlineklapu 

Can you please place this measure in your table and screenshot the results?

measure1 =
    COUNTROWS (
        CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Case], Table[Date] ) )
    )

[charlineklapu]

Sorry, for the delay.

Find the measure attached.

Am I doing something wrong?

[tamerj1]

@charlineklapu 

My friend. Please keep the four columns (case, date, name days) in the table

[charlineklapu]

 This works.

As soon as I also want to display "name", the error message appears.

Is it due to my model? I have a fact table and numerous dimension tabels. But I am missing one dimension, thats why I am connecting a table "GW Aktuell" where I am grouping users (found in dimension table) into groups. And I want to show only one duration per group (multiple users out of fact table into one group).