Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PowerBi Desktop

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 new.JPG

ers imp.JPG

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

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

 

You can measure like

count(Table[Date]) +0

 

or distinctcount(Table[Date]) +0

Anonymous
Not applicable

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 slicernew2.JPG

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Pragati11
Super User
Super User

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.