Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
@Anonymous , You can not have a column dependent on slicer.
You can measure like
count(Table[Date]) +0
or distinctcount(Table[Date]) +0
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
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
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)
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
@Anonymous - Columns are not dynamic but are created on data load/refresh, you will need to use a measure.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |