PowerBi Desktop

[Anonymous]

I wanted to coloum in my table which will give 1/0 when I select a particular date in slicer .If I slected 6/1/2019 in slicer should  should 1 value for it and 0 for others. As I wanted to duplicate the table and apply the loookup to calculate varaince 

ers 

[amitchandak]

@Anonymous , You can not have a column dependent on slicer.

You can measure like

count(Table[Date]) +0

or distinctcount(Table[Date]) +0

[Anonymous]

Hi i need to calculate actual variance by changing the base date and compair date dynamically without having duplicates in count is there any way using slicer

[v-shex-msft]

Hi @Anonymous,

Calculate column/table not respond and interaction with slicer/filters actions so you can't create dynamic calculated fields(column, table) based on filter or slicer. Perhaps you can create a table with the category you wanted then write a measure with switch function to display corresponding results based on the current category.

Since I do not so clear how your variance calculated, can you please share some more detail about these?
Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

Thankf for your reply ,I wanted to select 2 dates from  slicer suppose base date =1/1/19(A,B) and compare date=6/1/19(A,C,D,E)  then net variance = 2(4-2) and  actual varaince =3 (as C , D AND E are not there in base date) ,If base date =6/1/19(A,C,D,E) and  compair date = 1/1/19 then net variance =-2(2-4) and actual varinace = 1(Aas B is not there in base date)

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can use the following measure formals to get expected results based on ASC and DESC on selected dates.

net variance(ASC) =
VAR selected =
    ALLSELECTED ( table[Date] )
VAR minList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MINX ( selected, [Date] ) )
    )
VAR maxList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MAXX ( selected, [Date] ) )
    )
RETURN
    COUNTROWS ( maxList ) - COUNTROWS ( minList )

actual varaince(ASC) =
VAR selected =
    ALLSELECTED ( table[Date] )
VAR minList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MINX ( selected, [Date] ) )
    )
VAR maxList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MAXX ( selected, [Date] ) )
    )
RETURN
    COUNTROWS ( EXCEPT ( maxList, minList ) )

net variance(DESC) =
VAR selected =
    ALLSELECTED ( table[Date] )
VAR minList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MINX ( selected, [Date] ) )
    )
VAR maxList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MAXX ( selected, [Date] ) )
    )
RETURN
    COUNTROWS ( minList ) - COUNTROWS ( maxList )

actual varaince(DESC) =
VAR selected =
    ALLSELECTED ( table[Date] )
VAR minList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MINX ( selected, [Date] ) )
    )
VAR maxList =
    CALCULATETABLE (
        VALUES ( table[Employee] ),
        FILTER ( ALLSELECTED ( table ), [Date] = MAXX ( selected, [Date] ) )
    )
RETURN
    COUNTROWS ( EXCEPT ( minList, maxList ) )

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@Anonymous - Columns are not dynamic but are created on data load/refresh, you will need to use a measure.

[Pragati11]

Hi @Anonymous ,

Create a calculated column as follows and use it as a slicer:

TestCol = IF(tablename[Date] = '6/1/2019', 1, 0)

tablename[Date] - replace this with your table name and column name.

Thanks,

Pragati