Sort Matrix by dynamic column value based on filters

[afbraga66]

Hi,

I have a requirement to sort a matrix by the last possible month value, 7 in the example below. The issue is that Power BI only sorts by the total and that is not what I want.

I want it to look like the image below, i.e. sorted by the values where month equals 7, because it is the latest with the filters I have.

I did it with this formula:

Today Month = CALCULATE([mDuration(min)], KEEPFILTERS(DOWNTIME[MES_FABRIL]=MONTH(TODAY())))

The problem is that it is fixed to todays month, and I can't have it like that. I want it to have the dynamic filtering according to if I select 1 month, 2 months or none. If I select 5 and 6, then 6 should be the one being used for sorting since it is the latest. If I don't select anything then it is 7. Can you please help me making it dynamic?

Check the pbix file with the example above. PBI Test example 

Thank you.

Best regards,

André

[afbraga66]

Hi,

I have a requirement to sort a matrix by the last possible month value, 7 in the example below. The issue is that Power BI only sorts by the total and that is not what I want.

I want it to look like the image below, i.e. sorted by the values where month equals 7, because it is the latest with the filters I have.

I did it with this formula:

[Vmpbi]

Thanks for sharing… check out the links below. It provides a comprehensive guide on how to sort measures as columns 

in a matrix visual. 

https://insurancedatainsights.blog/2024/04/27/dynamic-data-display-matrix/ 

https://www.youtube.com/watch?v=k5FMfvPmkv4&t=190s 

[Anonymous]

Hi @afbraga66 ,

It's not supported to sort the matrix by specific column.

You could vote for this idea.

https://ideas.powerbi.com/ideas/idea/?ideaid=05c64cd1-c0d0-44d3-bceb-21f4acc0e548 

Best Regards,

Jay

[afbraga66]

Sorry to bother @Greg_Deckler , but add any luck with it? Thank you for helping me!

Best regards,

André

[afbraga66]

Hey@Greg_Deckler , is it possible to do that? Thank you 🙂

[Greg_Deckler]

@afbraga66 So like this?:

Today Month = 
    VAR __Date = MAX('DOWNTIME'[DIA_FABRIL])
RETURN
    CALCULATE([mDuration(min)], KEEPFILTERS(DOWNTIME[MES_FABRIL]=MONTH(__Date)))

Updated PBIX attached below sig.

[afbraga66]

Hello Greg,

Thank you so much for your help.

I tried your formula, but I have a issue with it. It's the fact that when the value is null it assumes in the total the latest value for the given months(148,05 from month 6). But in this case I need the 81 to be before the null in the sorting. Is there another way?

Best regards,

André

[Greg_Deckler]

@afbraga66 Perhaps:

Today Month = 
    VAR __Date = MAX('DOWNTIME'[DIA_FABRIL])
RETURN
    IF(ISBLANK([mDuration (min)],
      BLANK(),
      CALCULATE([mDuration(min)], KEEPFILTERS(DOWNTIME[MES_FABRIL]=MONTH(__Date)))
    )

[afbraga66]

Hey @Greg_Deckler ,

I have tried it, but it is not working. Still giving the same result as the previous one unfortunately.

Probably because the ISBLANK() evaluates against the values of all months in that expression?

Thank you.

[Greg_Deckler]

@afbraga66 What about this version?

Today Month = 
    VAR __Date = MAX('DOWNTIME'[DIA_FABRIL])
    VAR __mDuration = CALCULATE([mDuration(min)],KEEPFILTERS(DOWNTIME[MES_FABRIL]=MONTH(__Date)))
RETURN
    IF(ISBLANK(__mDuration),
      BLANK(),
      __mDuration
    )

[afbraga66]

Hey @Greg_Deckler ,

Sorry to bother but any news on this topic?

[afbraga66]

Hey @Greg_Deckler ,

Same result unfortunately 😞