Showing Files exist in previous date and not in current date

[Anonymous]

Hello Everyone,

assume we have this data ;

Date	File
08/04/2020	A
08/04/2020	B
08/04/2020	A
08/04/2020	C
09/04/2020	A
09/04/2020	B
09/04/2020	C
10/04/2020	A
10/04/2020	C

the table below displays the number of files per date and the difference between each update

Date	Number Of files	Difference
08/04/2020	4	0
09/04/2020	3	1
10/04/2020	2	1

So, The question is I want to use tooltips in power bi in order if:

•  I hover mouse on the Difference column for example 1 it must give me which file exists on 08/04/2020 and not on 09/04/2020 in our example is A: because A used 2 times on 08/04/2020 and 1 time in 09/04/2020

The tooltip will be in another report which will be related to the first one.

Thanks in Advance.

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

According to my knowledge, table visual does not support add customize tooltips. You can use a measure to replace it.

Please try:

First, crreate a new table

Table 2 = CROSSJOIN( VALUES('Table'[Date]),VALUES('Table'[File]))



Current Date Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Date]=EARLIER('Table 2'[Date]) && [File]=EARLIER('Table 2'[File])))+0



Previous Date Count = 
var _pre=MAXX(FILTER('Table',[Date]<EARLIER('Table 2'[Date])),[Date])
return IF(_pre=BLANK(),[Current Date Count], CALCULATE(COUNTROWS('Table'),FILTER('Table',[Date]=_pre && [File]=EARLIER('Table 2'[File])))+0)

Output:

Then create measure:

Number of files = COUNTROWS('Table')


Difference = 
var _pre=MAXX(FILTER(ALL('Table'),[Date]<MAX('Table'[Date])),[Date])
var _precount=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),[Date]=_pre))
return IF(_precount=BLANK(),BLANK(), _precount -COUNTROWS('Table'))


Tooltip = CONCATENATEX(FILTER('Table 2',[Date]=MAX('Table'[Date]) && [Current Date Count]<>[Previous Date Count]),[File])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Anonymous 

please try

Tooltip =
VAR CurrentDate =
    MAX ( TableName[Date] )
VAR NextDates =
    FILTER ( ALLSELECTED ( TableName[Date] ), TableName[Date] > CurrentDate )
VAR NextDate =
    MINX ( NextDates, TableName[Date] )
VAR CurrentFiles =
    VALUES ( TableName[File] )
VAR NextFiles =
    CALCULATETABLE (
        VALUES ( TableName[File] ),
        TableName[Date] = NextDate,
        REMOVEFILTERS ( TableName )
    )
RETURN
    CONCATENATEX (
        EXCEPT ( CurrentFiles, NextFiles ),
        [File],
        UNICHAR ( 10 ),
        [File], ASC
    )