Hello, I don´t know if this is possible or if there is another possible solution for this: Context:I have a DB with all the dates of the steps in the sales funnel.I want to create a funnel with the daily sales - I want to see what happened on the day that I select. "AND NOW" I want to compare different years in every step. This is how I have it now: e.g.Leads_2021 =VAR a = CALCULATE(DISTINCTCOUNT(Leads[ID_LEAD_SF]),FILTER(Preinscripciones,[EsLead]=1),'Date'[Year]=2021, USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))RETURN IF(ISBLANK(a),0,a) Leads_2022 =VAR a = CALCULATE(DISTINCTCOUNT(Leads[ID_LEAD_SF]),FILTER(Preinscripciones,[EsLead]=1),'Date'[Year]=2022, USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))RETURN IF(ISBLANK(a),0,a) EsLead =VAR a = MAX(Leads[F_ORIGEN_LEAD])RETURN IF(ISBLANK(a),0,1) "AND NOW" I want to compare different years (but in an independent way 2019 vs 2022, 2021 vs 2022, etc..).I was thinking of putting two slices each one with the year that the person wants to compare, creating a measure with that value, and putting that measure in the measure of the steps. So instead of the measure "Leads_2021" would be -> "Leads_year1" and "Leads_2022" like ->Leads_year2" I tried but it doesn't work or maybe I don't know the right way to make it work. ¿How can I create a measure with the value of the year that I selected in one of the two slicers so I can put it in the measure "Leads_year1" =? Year1 = VALUES('Date'[Year]) Leads_year1 =VAR a = CALCULATE(DISTINCTCOUNT(Leads[ID_LEAD_SF]),FILTER(Preinscripciones,[EsLead]=1),FILTER('Date','Date'[Year]=[Year1]), USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))RETURN IF(ISBLANK(a),0,a) Thank you in advance.

Hi Anonymous , Nice work! It looks like you are looking for SELECTEDVALUE( table[column] ). This function returns the value of the 'column', IF exactle on value is selected in the filter context. The complete solution could have the following elements: A. Two tables with years - one for year1 and another for year2. Lets call them 'Years1' and 'Years2', respectively. Each have one column 'Year'.B. Two slicers: One for each of the tables. Both slicers shoud be configured to enforce single select.C. Two measures to evaluate which year is selected. Measure 1 =IF( HASONEVALUE ( Years1 [Year] ),SELECTEDVALUE ( Years1 [Year]),"Error") Measure 2 ist the same, but with the table Years2. The IF-Condition with HASONEVALUE is not strictly necessary, if you enforce a single select through the slicers, but it's good practice if you want to use the measures in other contexts too. I hope that's what you need. Kind regards

Anonymous

Not applicable

4 years ago

Solved

Filter in a measure

Hello,

I don´t know if this is possible or if there is another possible solution for this:

Context:

I have a DB with all the dates of the steps in the sales funnel.
I want to create a funnel with the daily sales - I want to see what happened on the day that I select.
"AND NOW" I want to compare different years in every step.

This is how I have it now:

e.g.
Leads_2021 =
VAR a = CALCULATE(DISTINCTCOUNT(Leads[ID_LEAD_SF]),FILTER(Preinscripciones,[EsLead]=1),'Date'[Year]=2021, USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))
RETURN IF(ISBLANK(a),0,a)

Leads_2022 =
VAR a = CALCULATE(DISTINCTCOUNT(Leads[ID_LEAD_SF]),FILTER(Preinscripciones,[EsLead]=1),'Date'[Year]=2022, USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))
RETURN IF(ISBLANK(a),0,a)

EsLead =
VAR a = MAX(Leads[F_ORIGEN_LEAD])
RETURN IF(ISBLANK(a),0,1)
- "AND NOW" I want to compare different years (but in an independent way 2019 vs 2022, 2021 vs 2022, etc..).
  I was thinking of putting two slices each one with the year that the person wants to compare, creating a measure with that value, and putting that measure in the measure of the steps.
  
  So instead of the measure "Leads_2021" would be -> "Leads_year1" and "Leads_2022" like ->Leads_year2"
  
  I tried but it doesn't work or maybe I don't know the right way to make it work.
  
  ¿How can I create a measure with the value of the year that I selected in one of the two slicers so I can put it in the measure "Leads_year1" =?
  
  Year1 = VALUES('Date'[Year])
  
  Leads_year1 =
  VAR a = CALCULATE(DISTINCTCOUNT(Leads[ID_LEAD_SF]),FILTER(Preinscripciones,[EsLead]=1),FILTER('Date','Date'[Year]=[Year1]), USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))
  RETURN IF(ISBLANK(a),0,a)
  
  Thank you in advance.

Need Help

CerebusBI
4 years ago
Hi Anonymous ,

Nice work!

It looks like you are looking for SELECTEDVALUE( table[column] ).

This function returns the value of the 'column', IF exactle on value is selected in the filter context.

The complete solution could have the following elements:

A. Two tables with years - one for year1 and another for year2. Lets call them 'Years1' and 'Years2', respectively. Each have one column 'Year'.
B. Two slicers: One for each of the tables. Both slicers shoud be configured to enforce single select.
C. Two measures to evaluate which year is selected.

Measure 1 =
IF( HASONEVALUE ( Years1 [Year] ),
SELECTEDVALUE ( Years1 [Year]),
"Error"
)

Measure 2 ist the same, but with the table Years2.

The IF-Condition with HASONEVALUE is not strictly necessary, if you enforce a single select through the slicers, but it's good practice if you want to use the measures in other contexts too.

I hope that's what you need.

Kind regards

4 Replies

CerebusBI
Resolver I
4 years ago
Hi Anonymous ,

Nice work!

It looks like you are looking for SELECTEDVALUE( table[column] ).

This function returns the value of the 'column', IF exactle on value is selected in the filter context.

The complete solution could have the following elements:

A. Two tables with years - one for year1 and another for year2. Lets call them 'Years1' and 'Years2', respectively. Each have one column 'Year'.
B. Two slicers: One for each of the tables. Both slicers shoud be configured to enforce single select.
C. Two measures to evaluate which year is selected.

Measure 1 =
IF( HASONEVALUE ( Years1 [Year] ),
SELECTEDVALUE ( Years1 [Year]),
"Error"
)

Measure 2 ist the same, but with the table Years2.

The IF-Condition with HASONEVALUE is not strictly necessary, if you enforce a single select through the slicers, but it's good practice if you want to use the measures in other contexts too.

I hope that's what you need.

Kind regards
- Anonymous
  Not applicable
  4 years ago
  Thank you for your answer CerebusBI
  
  It seems to work for the overall total 😊, but I don't know why when I put that measure in the matrix it doesn't work for the subcategories 😥.
  
  I don't have any relationship with the two tables that I have created (because I just want the value of the year to filter -> FILTER('Date','Date'[Year]=[Measure 1]), I thought it was that but when I create the relationship the numbers in the matrix are wrong...
  
  Do you think is there something about the relationships?
  
  Kind regards,
  Dulce
  - Anonymous
    Not applicable
    4 years ago
    Sorry, I saw the mistake, it was the "edit interactions"... That I had changed before. But now it works :).
    
    Thank you very much.
CerebusBI
Resolver I
4 years ago
Phew, for a moment I was worried. 😉
I'm glad that it works now. 👍

Forum Discussion

Filter in a measure

4 Replies

Related content

Dynamic measure filtering

Filter by Rank measure

Using measure as a filter

Measure not affected by filter

DAX MEASURE TO FILTER