Forum Discussion

mariogonzra's avatar
mariogonzra
New Member
2 years ago
Solved

Measure ALLSELECTED not being affected by columns

 Hi, everyone:

 

I'm starting in PowerBI and I have a simple doubt that may be easy to resolve, but cannot find anywhere.

I have the following Matrix or whichever visual and I would like to create a mesaure that returns the value for the category "all" to all the lines. However, I cannot seem to get it right. The measure I'm using is the following

 

age.patients.total = CALCULATE(SUM(Table[patients], FILTER(ALLSELECTED(Table),Table[Age] = "All"). But as you can see below, it will work taking into account the outside filters and the row context but not for the columns (it's adding up the values for the different quarters/columns). I know the ALLSELECTED functions makes the measure to ignore the query filters, but is there any way around this?

 

 

Can anyone help?

 

Thanks,

 

Mario

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Ritaf1983 , thank for your concern about the problem, and i want to offer some more information for user to refer to.

    hello mariogonzra , based on your description, if your date column is based on a date table, you can try the following solution.

    Sample data

    Create a calendar table and create a one-many relationship between tables.

    Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

    Then create a measure

    age.patients.total =
CALCULATE (
    SUM ( 'Table'[patients] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Age] = "All"
            && YEAR ( [Date] ) = YEAR ( MAX ( 'Calendar'[Date] ) )
            && QUARTER ( [Date] ) = QUARTER ( MAX ( 'Calendar'[Date] ) )
    )
)

    Put the following field to the matrix

     

    Output

    Best Regards!

    Yolo Zhu

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

     

2 Replies