topic CurrentUser vs SelectedUsers in DAX Commands and Tips

CurrentUser vs SelectedUsers

WinterMist — Tue, 05 Dec 2023 23:04:50 GMT

Hello Community -

PBIX can be found here:

https://drive.google.com/file/d/1ilGCO8C9UYytxPUA_NOcff31Xh7lwN5K/view?usp=sharing

Measure 01 builds a concatenated list of selected User ID's from the slicer.

Measure 02 gets the current User ID in each row of the table visual.

Measure 03:

- I can't get M03 to work. But this is what it's supposed to do.

- IF(M02 IN M01, 1, 0)

NOTE 1: Without M03 in the table visual, 9 rows are correctly displayed (see screenshot 1 below). However, if M03 is added, the table visual "explodes" to show a high number of rows that should not appear, as well as incorrect data. (see screenshot 2 below)

NOTE 2: As you can see from the first screenshot, the slicer "ID-User" does NOT filter the table visual.

DESIRED RESULT: The first table visual (with 9 rows) should appear, and M03 should only show as 1 (or "True") for the Selected Users (in this case, only the rows where User = John). The other 5 rows (Ed, Jane & Sarah) should show 0 (or "False") because they are not in the currently selected set of users.

Regards,

Nathan

Re: CurrentUser vs SelectedUsers

Anonymous — Wed, 06 Dec 2023 05:17:07 GMT

Hi WinterMist ,

Please follow these steps:

Add two tables ,DO NOT create relationships.

(1)Table

Table = SUMMARIZE ( City, City[City ID], City[City Name], City[ID-City] )

(2)Table 2

Table 2 = SUMMARIZE ( User, User[User ID], User[User Name], User[ID-User] )

Relationship

add new measure Table_City id & Table_ID-User

Table_City id

Table_City id = VAR _1 = SELECTEDVALUE ( 'Table'[ID-City] ) VAR _2 = IF ( MAX ( City[ID-City] ) = _1, MAX ( Sales[City ID] ), BLANK () ) RETURN _2

Table_ID-User

Table_ID-User = VAR _1 = SELECTEDVALUE ( 'Table 2'[ID-User] ) VAR _2 = IF ( MAX ( User[ID-User] ) = _1, MAX ( User[User Name] ), BLANK () ) RETURN _1

final output

March or not = VAR _1 = CALCULATE ( MAX ( User[User Name] ), FILTER ( ALL ( User ), User[ID-User] = [Table_ID-User] ) ) RETURN IF ( [Table_City id] <> BLANK () && MAX ( User[User Name] ) = _1, 1, 0 )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

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

Re: CurrentUser vs SelectedUsers

WinterMist — Fri, 08 Dec 2023 18:09:07 GMT

Anonymous

So I really appreciate your time.

This definitely solves the 1 exact scenario I gave.

Unfortunately, if I add even one additional value to the user slicer, then it breaks.

For instance, if I add User "3-Sarah", then all values for selected users incorrectly go to 0.

Additionally, if no slicer values are selected, then nothing appears in the table visual at all, which is also a problem.

(City slicer should still be filtering the table visual at all times, but it's not anymore now that the slicer is coming from a disconnected table.)

Please keep in mind these slicers are multi-selection.

This is the reason why I have measures which build concatenated lists of the selected values, described in the initial post (as well as in the PBIX).

I also noticed, that you removed the visual cards from the PBIX which display these selected values.

I guess that's fine if we don't display them in a card.

But the solution still needs to be able to see ALL selected values.

NOTE: In the screenshot below, the output of the table visual is incorrect. It should show the data from New York & Los Angeles, because those are the selected cities which should define what appears in the table visual. But also note that I have recreated the cards which hold the selected concatenated lists for each slicer. (multi-selection lists).

Regards,

Nathan

P.S. This solution seems incredibly complex for what I am trying to do.

1) I already have the User ID for each row ("02 Current User ID" shown below).

2) I already have the concatenated list of selected users ("01 Selected Users" shown below).

All I want is 1 more column that checks if #1 exists in #2.

Is it really necessary to create copies of existing tables to do this?

(Forgive my ignorance, but I would think this just needs the creation of 1 more DAX formula that I'm struggling to figure out.)