Forum Discussion

charlineklapu's avatar
charlineklapu
Frequent Visitor
4 years ago
Solved

Measure filter min value per category

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:

CaseDateNameDays
1016.04.2022 00:00a2659,21
1016.04.2022 00:00b22,42
2016.04.2022 00:00c67,45
2016.04.2022 00:00a57,29
3014.04.2022 00:00a5572,92
3014.04.2022 00:00e239,08
4014.04.2022 00:00d36,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:

 

CaseDateNameDaysFlag
1016.04.2022 00:00a2659,210
1016.04.2022 00:00b22,421
2016.04.2022 00:00c67,450
2016.04.2022 00:00a57,291
3014.04.2022 00:00a5572,920
3014.04.2022 00:00e239,081
4014.04.2022 00:00d36,811

 

and by filtering there will, one line per case:

 

CaseDateNameDaysFlag
1016.04.2022 00:00b22,421
2016.04.2022 00:00a57,291
3014.04.2022 00:00e239,081
4014.04.2022 00:00d36,811

 

 

Thank you!

DAX
measure
min
  • tamerj1's avatar
    tamerj1
    4 years ago

    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

12 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      charlineklapu
      Frequent Visitor
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi charlineklapu 
        Try 

        Flag =
VAR CurrentDate = 
    MAX ( Table[Date] )
VAR MinDaysValue =
    MINX (
        FILTER ( ALLSELECTED( Table[Case] ), Table[Date] = CurrentDate ) ),
        [Days]
    )
RETURN
    [Days] = MinDaysValue
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      charlineklapu
      Frequent Visitor
      4 years ago

      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?