cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JuhoP Frequent Visitor
Frequent Visitor

Calculate dynamic measure based on slicer selection

Hi Everyone,

 

I am total newbie for Power BI, just couple of days hands on. I have a problem that I have not been able to solve till now and I was wondering if you could help me?

 

I have used a slicer to select data to my clustered column chart. I used following measure to read slicer value (European syntax).

 

Selected =

CONCATENATEX(
ALLSELECTED('Table1'[Column]);
'Table1'[Column];
";"
)

 

My plan is to use Selected value in another measure to dynamically calculate the line value. I have used direct query to my SQL Server DB but that should not affect to these calculations.

 

I tried to use the Selected value in following formula:

 

Metrics1 = ABS(CALCULATE(MAX('FactTable'[Value]);'DimensionTable'[Column]=[Selected];LASTDATE('Dimension Date'[Calendar_Date]))/(CALCULATE(MIN('FactTable'[Value]);'DimensionTable'[Column]=[Selected];ALLSELECTED('Dimension Date'[Calendar_Date])))-1)

 

Error message: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

Your help would be highly appreciated!

 

Regards,

 

JuhoP

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Calculate dynamic measure based on slicer selection

Hi @JuhoP,

 

Could you try the formula below to see if it works? Smiley Happy

Metrics1 =
VAR s = [Selected]
RETURN
    ABS (
        CALCULATE (
            MAX ( 'FactTable'[Value] );
            'DimensionTable'[Column] = s;
            LASTDATE ( 'Dimension Date'[Calendar_Date] )
        )
            / (
                CALCULATE (
                    MIN ( 'FactTable'[Value] );
                    'DimensionTable'[Column] = s;
                    ALLSELECTED ( 'Dimension Date'[Calendar_Date] )
                )
            )
            - 1
    )

 

Regards

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Calculate dynamic measure based on slicer selection

Hi @JuhoP,

 

Could you try the formula below to see if it works? Smiley Happy

Metrics1 =
VAR s = [Selected]
RETURN
    ABS (
        CALCULATE (
            MAX ( 'FactTable'[Value] );
            'DimensionTable'[Column] = s;
            LASTDATE ( 'Dimension Date'[Calendar_Date] )
        )
            / (
                CALCULATE (
                    MIN ( 'FactTable'[Value] );
                    'DimensionTable'[Column] = s;
                    ALLSELECTED ( 'Dimension Date'[Calendar_Date] )
                )
            )
            - 1
    )

 

Regards

JuhoP Frequent Visitor
Frequent Visitor

Re: Calculate dynamic measure based on slicer selection

Hi!

 

Yes, it works beatifully! Thanks a million!