Grouping values into a column from a measure that use a slicer to change the value result

[Anonymous]

Hi,

 I am newish to BI so help would be much appreciated 🙂

I have a calculated measure in a table in PowerBi desktop report, to create some customer brackets based on Cash flow over past 30 days.

This measure is  looking at another measure below "Cash Last 30 Days Adjusted" which is generated & adjusted via sliders to indicate % increases, which impact on the adjusted cash bracket.
However I need to turn use this measure data in a matrix/chart and I cannot use this measure on the legend or axis on chart.

Here is what the table looks like at a customer level.

The last column is the measure "CashBracketAdjusted" I have created, based on the following DAX CashBracket Adjusted = IF(CustomerProfile[Cash Last 30 Adjusted] <=0, "5.Distressed", IF(CustomerProfile[Cash Last 30 Adjusted] <= 200, "4.Stretched", IF(CustomerProfile[Cash Last 30 Adjusted] <= 400, "3.Ticking Over", IF(CustomerProfile[Cash Last 30 Adjusted] <= 1000, "2.Comfortable", "1.Secure"))) .
I have tried to create this separate column that references the measure outputs, but it appears that this column doesnt update when I adjust the filters/sliders. Where I would expect to see a new result( cash bracket) on a row, it appears as it is just showing the original Cash Bracket value, and not the new one.

I also tried a different approach and have tried to create a column that references the Measure( "Cash Bracket Adjusted") and uses Variables as per the DAX below CashBracket Adjusted1 = VAR CASHADJUSTED = CustomerProfile[CashBracket Adjusted] VAR BRACKET = IF(CASHADJUSTED = "5.Distressed", "5.Distressed", IF(CASHADJUSTED = "4.Stretched", "4.Stretched", IF(CASHADJUSTED= "3.Ticking Over", "3.Ticking Over", IF(CASHADJUSTED = "2.Comfortable", "2.Comfortable", IF(CASHADJUSTED = "1.Secure", "1.Secure", "No"))))) RETURN BRACKET

Look forward to any ideas, cheers Matt

[v-lid-msft]

Hi @Anonymous ,

If you want to hide all the row, you should create a table contain the category first, then change all the measure you want to show in the matrix.

Sum Of Cash Last 30 Days =
VAR result =
    SUM ( CustomerProfile[Cash] )
VAR t =
    IF (
        result <= 0,
        "5.Distressed",
        IF (
            result <= 200,
            "4.Stretched",
            IF (
                result <= 400,
                "3.Ticking Over",
                IF ( result <= 1000, "2.Comfortable", "1.Secure" )
            )
        )
    )
RETURN
    IF ( t IN FILTERS ( CashBracket[Type] ), result, BLANK () )

Cash Last 30 Adjusted =
VAR result =
    SUM ( CustomerProfile[Cash] ) * -1
VAR t =
    IF (
        result <= 0,
        "5.Distressed",
        IF (
            result <= 200,
            "4.Stretched",
            IF (
                result <= 400,
                "3.Ticking Over",
                IF ( result <= 1000, "2.Comfortable", "1.Secure" )
            )
        )
    )
RETURN
    IF ( t IN FILTERS ( CashBracket[Type] ), result, BLANK () )

CashBracket =
VAR result =
    IF (
        CustomerProfile[Sum Of Cash Last 30 Days] <= 0,
        "5.Distressed",
        IF (
            CustomerProfile[Sum Of Cash Last 30 Days] <= 200,
            "4.Stretched",
            IF (
                CustomerProfile[Sum Of Cash Last 30 Days] <= 400,
                "3.Ticking Over",
                IF (
                    CustomerProfile[Sum Of Cash Last 30 Days] <= 1000,
                    "2.Comfortable",
                    "1.Secure"
                )
            )
        )
    )
RETURN
    IF ( ISBLANK ( [Sum Of Cash Last 30 Days] ), BLANK (), result )

CashBracket Adjusted =
VAR result =
    IF (
        CustomerProfile[Cash Last 30 Adjusted] <= 0,
        "5.Distressed",
        IF (
            CustomerProfile[Cash Last 30 Adjusted] <= 200,
            "4.Stretched",
            IF (
                CustomerProfile[Cash Last 30 Adjusted] <= 400,
                "3.Ticking Over",
                IF (
                    CustomerProfile[Cash Last 30 Adjusted] <= 1000,
                    "2.Comfortable",
                    "1.Secure"
                )
            )
        )
    )
RETURN
    IF ( ISBLANK ( [Cash Last 30 Adjusted] ), BLANK (), result )

the result var in the measure can change to your calculate formula.

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Dong Li,

That all works based on what you have sent through, thank you. However, when I try to use these results cash brackets on an axis of a chart or in rown or column headers in a table, it doesnt allow this.

The ultimate aim is to hopefully use these brackets to count the number of customers that  are within each, and  visualise via graph or matrix.
The data you have mocked up is fine , and thanks for this!

M

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

we can create following measure to count the user for each category.

CashBracket Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( CustomerProfile ),
            "adjustType", [CashBracket]
        ),
        [adjustType] IN FILTERS ( CashBracket[Type] )
    )
)

CashBracket Adjusted Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( CustomerProfile ),
            "adjustType", [CashBracket Adjusted]
        ),
        [adjustType] IN FILTERS ( CashBracket[Type] )
    )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much! Now my challenge is to do this across other measures and use more than 1 on a chart. Much appreciated!
M

[v-lid-msft]

Hi @Anonymous ,

Could you please describle more detail about across other measures if you still have problems?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...