Search code examples
powerbipowerbi-desktopslicers

Workaround to use slicer values in measures that behave like column calculations in powerBI

I'm trying to use slicer values as calculated column or something that works like one

I've seen this post

https://community.powerbi.com/t5/Desktop/Slicer-Value-in-Column-Formula/m-p/214892#M95071

but not sure how to proceed with the following case

I have registers from a sort of SCD with ValidStartDate and ValidEndDate

User should be able to set 2 slicers: AnalysisStartDate and AnalysisEndDate

I should be able to count registers based on those two dates, for instance

  • how many registers have ValidStartDate between AnalysisStartDate and AnalysisEndDate?

  • how many registers have ValidEndDate between AnalysisStartDate and AnalysisEndDate ?

Anyhelp appreciated

Solution

  • Looks like I managed to get to what i wanted

    First you need a "measure" version of the columns you want to use in calculation just using FIRSTDATE() for instance -- I think it's very important to create the measure in the same table

    To capture slicer value in a measure using something like:

    if it has one value, get the value, otherwise use first value (or whatever you want) 

    x Analisis Inicio = IF(HASONEVALUE(TD_FECHAS_INICIO[DT_ANALISIS_INICIO]);VALUES(TD_FECHAS_INICIO[DT_ANALISIS_INICIO]);FIRSTDATE(TD_FECHAS_INICIO[DT_ANALISIS_INICIO].[Date]))

    Now you can start creating measures which compare both 

    x SW_ES_ALTA = 
IF(
    AND([x Inicio Measure] >= [x Analisis Inicio] 
        ; [x Inicio Measure] <= [x Analisis Fin])
    ;"SI"
    ;"NO"
)

    and even counts of this last measure 

    x HC_ES_ALTA = COUNTAX(FILTER(ZZ_FLAGS_INMUEBLE;[x SW_ES_ALTA]="SI");ZZ_FLAGS_INMUEBLE[ID_INMUEBLE])

    Not the easiest path, and probably you can put several of these measures in a single one, but if it works, it works...