Filter in a measure

[Anonymous]

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.

[CerebusBI]

Phew, for a moment I was worried. 😉

I'm glad that it works now. 👍

[CerebusBI · Accepted solution]

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]

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]

Sorry, I saw the mistake, it was the "edit interactions"... That I had changed before. But now it works :).

Thank you very much.