DAX to facilitate filters whether True for group(s) selected

[Anonymous]

Below is an example of the dataset.  I am trying to display different users based on filter conditions.  We want to be able to select by whether or not the user has visited. 

We also want to be able to select some number of groups.  For visited = true, if visited is true for any of the groups selected for that user, then the username will be returned.  For visited = false, if visited is true for any of the groups selected, then the username will not be returned.  

USERNAME	GROUP	VISITED
jbearp@hotmail.com	ABC	TRUE
jbearp@hotmail.com	DEF	TRUE
jbearp@hotmail.com	JKL	FALSE
jbearp@hotmail.com	PQR	FALSE
wortmanj@me.com	ABC	TRUE
wortmanj@me.com	DEF	FALSE
wortmanj@me.com	GHI	FALSE
wortmanj@me.com	MNO
wortmanj@me.com	PQR	FALSE

EXAMPLE 1:

Filters applied: GROUP = ABC; VISITED = TRUE

Return:

jbearp@hotmail.com
wortmanj@me.com

EXAMPLE 2:

Filters applied: GROUP = DEF; VISITED = TRUE

Return:

jbearp@hotmail.com

EXAMPLE 3:

Filters applied: GROUP = ABC,DEF; VISITED = TRUE

Return:

jbearp@hotmail.com
wortmanj@me.com  (because user has a value of true for ABC or DEF selected)

EXAMPLE 4:

Filters applied: GROUP = DEF,PQR; VISITED = FALSE

Return:wortmanj@me.com  (because user has a value of false for both DEF and PQR)

I assume I will need a new measure/column to facilitate this, but I cannot figure out what that should be.  The following allows me to summarize by username if visited is TRUE, but I do not know how to get this to work with the group filter.

MASTER VISITED =
CONTAINS (
    CALCULATETABLE ( 'Data Table', ALLEXCEPT ( 'Data Table', 'Data Table'[Username] ) ),
    'Data Table'[Visited], "TRUE"
)

[Anonymous]

This code basically returns what I want.  When you select different combinations of groups in the filter, it does return the right true or false value for the user jbearp@hotmail.com . However, it is hard-coded for only that single user.  How can I make it work for any user? 

Also, I want to be able to filter by the "T" and the "F" that this measure outputs.  So how to build this logic in so that it works as a slicer?

Measure = 
VAR tempUser = "jbearp@hotmail.com"
VAR tempTable_TRUE =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table',
            "USERNAME", 'Table'[USERNAME],
            "GROUP", 'Table'[GROUP],
            "VISITED", 'Table'[VISITED]
        ),
        KEEPFILTERS ( VALUES ( 'Table'[GROUP] ) ),
        'Table'[VISITED] = TRUE
    )
VAR tempTable_FALSE =
    EXCEPT (
        CALCULATETABLE (
            SELECTCOLUMNS (
                'Table',
                "USERNAME", 'Table'[USERNAME],
                "GROUP", 'Table'[GROUP],
                "VISITED", 'Table'[VISITED]
            ),
            KEEPFILTERS ( VALUES ( 'Table'[GROUP] ) ),
            'Table'[VISITED] = FALSE
        ),
        tempTable_TRUE
    )
RETURN
    IF (
        CONTAINS ( tempTable_TRUE, [USERNAME], tempUser ),
        "T",
        IF (
            CONTAINS ( tempTable_FALSE, [USERNAME], tempUser ),
            "F",
            "No Permission"
        )
    )

[Anonymous]

@Anonymous, sorry to say that... I just can't help but wonder how you are going to understand this code in a month's time... Not to mention those that will have to work with this. I would think twice before putting such code in production.

Best
D

[Anonymous]

@Anonymous How do I exclude the blanks or ones where there is no group value from showing up in the False category on your solution?  There appears to only be a way to group users in two ways.

[Anonymous]

Here you are. I've attached the file this time. Note that the formula I've created is easier to understand and you should favour it over the solution above for the reasons I've already mentioned. And the formula deals correctly with BLANKS as well.

Best

D

[Greg_Deckler]

@Anonymous - I am not sure why you need to calculate anything. I was able to do all of your scenarios with just using slicers and a table visualization. See attached PBIX under sig.

[Anonymous]

@Greg_Deckler, you were "able" because you did not understand the requirements.

Best
D

[Greg_Deckler]

@Anonymous Ah the last one, missed it. For that one you need an AND slicer like this. The rest are just standard slicing.

Cohort = 
VAR tmpTable1 = 
    GENERATE(
        VALUES('Table'[USERNAME]),
            EXCEPT(
                VALUES('Table'[GROUP]),
                CALCULATETABLE(VALUES('Table'[GROUP])
            )
        )
    )
VAR tmpTable2 = SUMMARIZE(tmpTable1,'Table'[USERNAME])
VAR tmpTable3 = EXCEPT(VALUES('Table'[USERNAME]),tmpTable2)
RETURN CONCATENATEX(tmpTable3,[USERNAME],",")

Updated PBIX.

[Anonymous]

@Greg_Deckler, he does not ask for a string with the names but he wants to be able to display the names in a table as individual rows.

Best
D

[Greg_Deckler]

@Anonymous - That may very well be the case but the requirement was:

trying to display different users based on filter conditions

There was no mention of how to display them. Extra points if you can explain how the Cohort measure works.

[Greg_Deckler]

@Anonymous - This version allows you to switch between your use cases of having essentially desiring a standard slicer versus an AND slicer in different circumstances:

Cohort 2 = 
    VAR __Selected = SELECTEDVALUE('Table'[VISITED])
    VAR tmpTable1 = 
        GENERATE(
            VALUES('Table'[USERNAME]),
                EXCEPT(
                    VALUES('Table'[GROUP]),
                    CALCULATETABLE(VALUES('Table'[GROUP])
                )
            )
        )
    VAR tmpTable2 = SUMMARIZE(tmpTable1,'Table'[USERNAME])
    VAR tmpTable3 = EXCEPT(VALUES('Table'[USERNAME]),tmpTable2)
RETURN
    IF(__Selected,CONCATENATEX(SUMMARIZE('Table',[USERNAME]),[USERNAME],","),
    CONCATENATEX(tmpTable3,[USERNAME],","))

[Greg_Deckler]

@Anonymous if as @Anonymous has indicated that you want to display these in a table visualization versus a card visualization, use this version:

Cohort 3 = 
    VAR __Selected = SELECTEDVALUE('Table'[VISITED])
    VAR tmpTable1 = 
        GENERATE(
            DISTINCT(ALL('Table'[USERNAME])),
            EXCEPT(
                CALCULATETABLE(VALUES('Table'[GROUP]),REMOVEFILTERS('Table'[USERNAME])),
                CALCULATETABLE(VALUES('Table'[GROUP]))
            )
        )
    VAR tmpTable2 = SUMMARIZE(tmpTable1,'Table'[USERNAME])
    VAR tmpTable3 = EXCEPT(DISTINCT(ALL('Table'[USERNAME])),tmpTable2)
RETURN
    IF(__Selected,CONCATENATEX(SUMMARIZE('Table',[USERNAME]),[USERNAME],","),
    CONCATENATEX(tmpTable3,[USERNAME],","))

And then create this measure:

Measure = 
    VAR __User = MAX('Table'[USERNAME])
RETURN
    IF(SEARCH(__User,[Cohort 3],1,-1) = -1,0,1)

You can then use this measure in a Filter as demonstrated in the updated PBIX file. I think that should pretty much do it. It at least covers all of the requirements that you have listed and displays the correct results, in a table. 

[Anonymous · Accepted solution]

Thanks all for any input you provided that helped me work towards a solution.  Below is my working solution.

Create table as given in example, and create the following two tables in the model which connect to the main table.  These two tables will be used to filter the data.

VISITED

TRUE

FALSE

and 

GROUP

ABC

DEF

JKL

PQR

GHI

MNO

Then create a measure Output User (code below), which helps to determine whether or not a user should be included in the output.  This is needed to control for blank values, etc.  "Output User is 1" should be a filter condition on any output visuals.

Output User = 
-- Variable to count the number of TRUE values by user for selected groups.
VAR _VisitCount_TRUE =
    CALCULATE ( COUNTROWS(FILTER('Visits to Groups','Visits to Groups'[VISITED]=TRUE())), ALL ( Visited[VISITED] ) )
-- Variable to count the number of TRUE values by user for selected groups.  Does not count blank values.
VAR _VisitCount_FALSE =
    CALCULATE(COUNTROWS(FILTER('Visits to Groups','Visits to Groups'[VISITED]=FALSE() && NOT(ISBLANK('Visits to Groups'[VISITED])))),ALL ( Visited[VISITED] ))
--Variable to capture whether TRUE or FALSE filter is selected by user.
VAR _Visited =
    SELECTEDVALUE ( Visited[VISITED] )
RETURN
    IF (
        _Visited, 
        --if TRUE is selected
        IF (
            --If filter selection = TRUE && visit group visit TRUE count is greater than zero, then SHOW USER (1)
            _VisitCount_TRUE > 0,
            1,
            0
        ),
        --if FALSE is selected
        IF (
            --If filter selection = FALSE && visit group TRUE count is greater than zero, then DO NOT SHOW USER (0)
            _VisitCount_TRUE > 0,
            0,
            IF (
                --The purpose of this IF statement is to control for blank values of visit count.
                --If filter selection = FALSE && visit group FALSE count is greater than zero, then SHOW USER (1)
                _VisitCount_FALSE > 0, 
                1,
                0
            )
        )
    )

[Anonymous]

@Greg_Deckler Thank you!! This is very close!  One more scenario that I thought of when I was playing with your file...

EXAMPLE 6:

Filters applied: GROUP = JKL, GHI; VISITED = FALSE

Return:

jbearp@hotmail.com [even though he does not have permission for GHI, he does have a FALSE for JKL so should be returned]
wortmanj@me.com [similar]

[Greg_Deckler]

What about this variation?

Cohort 3 = 
    VAR __Selected = SELECTEDVALUE('Table'[VISITED])
    VAR __Table = 'Table'
    VAR tmpTable1 = 
        GENERATE(
            DISTINCT(ALL('Table'[USERNAME])),
            EXCEPT(
                CALCULATETABLE(VALUES('Table'[GROUP]),REMOVEFILTERS('Table'[USERNAME])),
                CALCULATETABLE(VALUES('Table'[GROUP]))
            )
        )
    VAR tmpTable2 = SUMMARIZE(tmpTable1,'Table'[USERNAME])
    VAR tmpTable3 = EXCEPT(DISTINCT(ALL('Table'[USERNAME])),tmpTable2)
RETURN
    IF(
        __Selected,
        CONCATENATEX(SUMMARIZE('Table',[USERNAME]),[USERNAME],","),
        CONCATENATEX(__Table,[USERNAME],",")
    )

[Anonymous]

@Greg_Deckler, why would I want to try to explain how it works? I don't feel such a need.

Best
D

[Greg_Deckler]

@Anonymous - Well, you would have to understand some pretty esoteric DAX to figure it out. I've never met anyone that could explain why it works. But I do explain it in my book that comes out next week DAX Cookbook. 🙂

[Anonymous]

Sadly, your solution, @Greg_Deckler, is way more complex than it should be. Mine is easy (there above...). Things should be simple, Mr Deckler, not complex without a real need.

If you've never met anyone who could explain the piece of code, then it only means you've not met enough people.

Best
D

[Anonymous]

Have a good look at the file attached as there are pitfalls you should be aware of.

https://1drv.ms/u/s!ApyQEauTSLtOgYMtieUhiqboe05dsw?e=lt2EbL

By the way, please do not fall into the trap of thinking that with small models you don't need to apply the proper dimensional design. YOU HAVE TO if you want to be on the safe side.

Best

D

[Anonymous]

@Anonymous This is great! Thank you!! So far it is working well, however here is a case where it does not work.

EXAMPLE 5:

Filters applied: GROUP = JKL; VISITED = FALSE

Return:

jbearp@hotmail.com

The user wortmanj@me.com should not show up in this because the group JKL does not apply to him, so he doesn't have the opportunity to be true/false.  The FALSE group is not a catchall for the blanks as well, it should only pickup the true FALSE's.