Measure returning value for a given value in the column

[Anonymous]

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. I have work with some DAX in Excel, where the following DAX measure worked:

age.patients.total = CALCULATE(SUM(Table[patients], Table[Age] = "All"). But as you can see below, it only works, when the row refers to the "All" category. Has this got something to do with the "scope"?

The filters applied are these:

It's been bugging for some time, because as I said, this works in Excel.

Can anyone help?

Thanks,

Mario

[jgeddes]

Within the context of your matrix visual your measure is doing exactly what you have written. 

It is returning the sum of patients when the age is 'all'.

For rows that are not 'all' (example 'child') the measure will not return anything.

If you want the sum of patients when the age category is 'all' displayed for all of the rows then you will need to explore the ALL functions.

In this case something like the following measure may work for you.

All Sum = 
CALCULATE(
    SUM('Table'[patients]),
    FILTER(ALL('Table'), 'Table'[Age] = "All")
)

What you are referring to as 'scope' is more commonly called context in the Power BI world. Understanding the context that is being applied to measures is critical to understanding which filtering functions are required to get your desired result.

https://learn.microsoft.com/en-us/dax/all-function-dax 

[Anonymous]

Hi @jgeddes !

Thanks a lot for your prompt answer. However, I already tried that solution; but the visual it's the measure does not seem to be affected by the rest of the slicers/filters. I guess that has to do with the ALL function: 

Thanks a lot though!

Mario

[jgeddes]

Yes the result you are seeing is due to the ALL function. You will likely need to use an ALLSELECTED or ALLEXCEPT function. It is impossible to say for sure without seeing the dataset. When you use the ALL function you are literally getting ALL of the rows from the table in question and then applying context from there.

[Anonymous]

Hi, again:

Thanks a lot for your help. I used ALLSELECTED and now data is affected by every slicer in the page, so I am getting pretty close. However, it is not affected the columns in the matrix:

Thanks a lot for the help again. Getting a hold of the context may take some time...

Mario

[Anonymous]

Hi @Anonymous ,

Did @jgeddes 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng