To get list of users from the selected date in a table visual

[Lahari]

Hi @Sahir_Maharaj  and All, 

I have a scenario where i need to compare list of users and their roles based on selected dates which is named as prior date and current date. This prior date is from the Comparision_Livedate(Calendartable1) and current date is coming from the Live_Date(Calendartable2). Here user will select in date slicer of his own, prior date and current date.

The prior and current dates are connected to main table REM_ROLE_ACCESS_AUDIT_DATA_RPT by column name Row_Load_Date.  the prior date - table REM_ROLE_ACCESS_AUDIT_DATA_RPT is having inactive relationship and current date  -REM_ROLE_ACCESS_AUDIT_DATA_RPT  is haivng active relationship.

 I am getting count of users and roles correctly with measures but the enduser need a table visual which shows list of prior users, prior roles, current users and current roles. by comparing the prior and current when the user and role is not present in prior but present in current then the prior user and role should be blank and highlighted with a color and in the same if user and role is not present in current but present in prior then the current user and current role should be blank and highlighted with a color.  if the user and role is present in both prior and current then it should be shown normally. 

In power bi the table visual is as follows

This is the output i am expecting for the list of users and roles both for prior  and current dates.

The above pbi table visual is achieved by conditional formatting of column Full_Name and Role based on the count of prior roles and users, the count of current roles and users. but when the end user export this table visual as excel then conditional formatting of color is not exported and the enduser can see the list of full_name and roles even when they are not present in prior or current so they need to be formatted in power bi table visual itself based on the condition if they Full_Name and role is not presented then it should be blank. 

for the count of users and roles based on prior date and current date measures are as follows

count of prior users mesaure is

SD P3 Prior Period Users =

var _MINDATE = MIN(Comparision_LiveDate[Date])

var b = CALCULATE([P3 Prior Period Users],TREATAS({_MINDATE},Comparision_LiveDate[Date]))

RETURN

DIVIDE(b,[SD P3 Normalisation Factor])

 -------------------------------------------------------------

P3 Prior Period Users = var a = CALCULATE([Current Period Users],ALL(LiveDate),USERELATIONSHIP(LiveDate[Date],Comparision_LiveDate[Date]))*[Normalization Factor]

var b = IF(ISBLANK(a),0,a)

return b

 ---------------------------------------------------------------------

Current Period Users = var a = CALCULATE(DISTINCTCOUNTNOBLANK(REM_ROLE_ACCESS_AUDIT_DATA_RPT[FULL_NAME]),LiveDate[DateID],REM_ROLE_ACCESS_AUDIT_DATA_RPT[DateID_RLD])

var b = IF(ISBLANK(a),0,a)

return b

-----------------------------------------------------------

SD P3 Normalisation Factor =

var _MINDATE = MIN(Comparision_LiveDate[Date])

var b = CALCULATE([Normalization Factor],TREATAS({_MINDATE},Comparision_LiveDate[Date]))

RETURN

b

------------------------------------------------------------------------

Normalization Factor = COUNTROWS ( 'LiveDate' ) / COUNTROWS ( 'Comparision_LiveDate' )

--------------------------------------------------

the same for the count of prior roles where it is taken from the table of column roles.

for the count of current users and roles mesaures are as follows

count of current period users measure is 

SD P3 Current Period Users =

var _MINDATE = MIN(LiveDate[Date])

RETURN

CALCULATE([P3 Current Period Users],TREATAS({_MINDATE},LiveDate[Date])

)

 -----------------------------------------------------------------

P3 Current Period Users =

var a = CALCULATE(DISTINCTCOUNTNOBLANK(REM_ROLE_ACCESS_AUDIT_DATA_RPT[FULL_NAME]),LiveDate[DateID],REM_ROLE_ACCESS_AUDIT_DATA_RPT[DateID_RLD])

var b = IF(ISBLANK(a),0,a)

return b

 --------------------------------------------------------

the same for the count of current roles where it is taken from the table of column roles

Now here I tired many ways to get list of users and roles in prior by taking measure

Measure for the list of prior period users .

SD P3 Prior Period Users =

var _MINDATE = MIN(Comparision_LiveDate[Date])

var b = CALCULATE([P3 Prior Period Users],TREATAS({_MINDATE},Comparision_LiveDate[Date]))

var c = SELECTEDVALUE(REM_ROLE_ACCESS_AUDIT_DATA_RPT[FULL_NAME])

RETURN

if(DIVIDE(b,[SD P3 Normalisation Factor]) = 0, BLANK(), c)

-----------------------------------------------------------

 here i have written a condition that if prior user is 0 then blank otherwise it should take column of Full_Name from the REM_ROLE_ACCESS_AUDIT_DATA_RPT((list of users are filtered based on the prior date selected from slicer with the Row_Load_date from the table REM_ROLE_ACCESS_AUDIT_DATA_RPT) in the same way for role as well.

for the current periord list of users and roles measure is as follows

List of current perior users measure is 

Current users = IF([SD P3 Current Period Users] > 0,SUMMARIZECOLUMNS(REM_ROLE_ACCESS_AUDIT_DATA_RPT[FULL_NAME]),BLANK())

--------------------------------------------------------------

same measure but taking column from roles of a table to get list of roles ( list of users are filtered based on the current date selected from slicer with the Row_Load_date from the table REM_ROLE_ACCESS_AUDIT_DATA_RPT). 

 i have written same condition for current user and current role as when it is 0 it should be blank otherwise it should be list of Full_Name from the table REM_ROLE_ACCESS_AUDIT_DATA_RPT. 

The measures are working fine for current user and roles to get list but when coming to prior user and role for list it showing blank even it is 1 and in my view the logic is its taking the list of users and roles from the current date only but not fromt the prior so its showing blank. now i need to do changes in the measure of prior user list and role list that to filter the table REM_ROLE_ACCESS_AUDIT_DATA_RPT, Full_Name column for the prior date and as the relationship is inactive between table REM_ROLE_ACCESS_AUDIT_DATA_RPT and prior date we need to make it active by userrelationship. 

so once this two conditions are considered in the measure then we can have list of users and roles for the prior date as well. 

---------------------------------------------------

The measure to be modified here is 

SD P3 Prior Period Users =

var _MINDATE = MIN(Comparision_LiveDate[Date])

var b = CALCULATE([P3 Prior Period Users],TREATAS({_MINDATE},Comparision_LiveDate[Date]))

var c = SELECTEDVALUE(REM_ROLE_ACCESS_AUDIT_DATA_RPT[FULL_NAME])

RETURN

if(DIVIDE(b,[SD P3 Normalisation Factor]) = 0, BLANK(), c)

------------------------------------------------

But i tried to modify the measure for the list of prior users but i am not able to get desired output. So help me in solving this measure as per the desired output.

if any other approach is their to solve this issue then i can work in that perspective as well. 

please help me to solve this issue. Thanks in advance. 

[Sahir_Maharaj]

Tagging @Greg_Deckler, @amitchandak, @tamerj1, @lbendlin, @Ashish_Mathur, @MFelix  for support 🙂