Show member to display in 1 group when they belong to 2 groups; avoid duplicate display of member

[Anonymous]

Pulling data from Active Directory for a dashboard...

ALL users are in IntuneMDMUsers group.
Some are in FLW, Office Worker or Exec group.
Problem is there are some users ONLY in IntuneMDMUsers group and NOT with the other groups.

If a user is NOT in either FLW, Office Worker or Exec group, I need to display that user is in the IntuneMDMUsers group.

So if I am in FLW and IntuneMDMUsers group; I want to display FLW.
Another user is ONLY in IntuneMDMUsers group, but will not display it becuse if I select to view IntuneMDMUsers also in the filter, it will display a user TWICE belonging to each group.

So, if I allow the dashboard to view users in the IntuneMDMUsers group, it will display most users twice in IntuneMDMUsers AND either FLW, Office Worker or Exec.

How can I get the dashboard filter to only display one group even if they belong to 2 ?

So if they belog to 2 groups, it displays they belong to FLW, Office Worker or Exec.

If they belong to ONLY IntuneMDMUsers, it will display that user and NOT display a user twice belonging to each group.

Example display:

Filter is set to view users in IntuneMDMUsers, FLW, Office Worker and Exec.

Paul is only in Intune MDM User, but James is displayed TWICE because he belongs to both groups.

I want to have James display only in FLW even though he's in both groups.

[Anonymous]

Method 2 was great resolution, NOW I need one more tweak in my dashboard if you can help.

If "Owner Type"="Corporate" and "AD Group"="Intune MDM User"...then "AD Group" should display "FLW" only; not "Intune MDM User"

If "Owner Type" = "Corporate" and member of 2 AD Groups = AD Group should display "FLW", "Office Worker" or "Exec" and NOT "Intune MDM User" (like your Measure does now)

If "Owner Type" = "Personal", then AD Group should display "NONE"

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this could meet your requirements:

Method 1: Transform data in Power Query Editor.

Create a blank query and put the code below in Advance Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKc1LVfB18VUILU4tUtJR8i4qTU1PLdJRCEgszVHwUorViVZy8wkHyoRkZKbmpCp4AeW8EnNTixUci/LywfKYpuBQGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Users = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Users", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Users"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Group=nullable text, Users=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Group"}, {"Group"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [Count]=2 and [Group]="Intune MDM User" then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Custom"})
in
    #"Removed Columns"

Method 2: Use measure as a visual filter in report view.

Measure =
VAR GroupCount_ =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Group] ),
        ALLEXCEPT ( 'Table', 'Table'[Users] )
    )
VAR CurrentGroup_ =
    SELECTEDVALUE ( 'Table'[Group] )
RETURN
    IF ( GroupCount_ = 2 && CurrentGroup_ = "Intune MDM User", 0, 1 )

For more details, please check the attached .pbix file.

Best Regards,

Icey

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

[Anonymous]

Method 2 was great resolution, NOW I need one more tweak in my dashboard if you can help.

If "Owner Type"="Corporate" and "AD Group"="Intune MDM User"...then "AD Group" should display "FLW" only; not "Intune MDM User"

If "Owner Type" = "Corporate" and member of 2 AD Groups = AD Group should display "FLW", "Office Worker" or "Exec" and NOT "Intune MDM User" (like your Measure does now)

If "Owner Type" = "Personal", then AD Group should display "NONE"

[Anonymous]

Method 2: Use measure as a visual filter in report view.

THIS WORKED...THANK YOU !!!

[Anonymous]

I appreciate the response, HOWEVER your example is splitting the user's first and last name.  Is this correct ?

[Icey]

Hi @Anonymous ,

Sorry, I mistakenly thought there were two people.😅 I have corrected in my previous reply. Please check.

Best Regards,

Icey

[Anonymous]

Method 2 was great resolution, NOW I need one more tweak in my dashboard if you can help.

If "Owner Type"="Corporate" and "AD Group"="Intune MDM User"...then "AD Group" should display "FLW" only; not "Intune MDM User"

If "Owner Type" = "Corporate" and member of 2 AD Groups = AD Group should display "FLW", "Office Worker" or "Exec" and NOT "Intune MDM User" (like your Measure does now)

If "Owner Type" = "Personal", then AD Group should display "NONE"

[Icey]

Hi @Anonymous ,

Please check if this could meet your requirements:

AD Group Measure = 
VAR CurrentGroup_ =
    SELECTEDVALUE ( 'Table'[Group] )
VAR CurrentType_ = 
    SELECTEDVALUE ( 'Table'[Owner Type] )
RETURN 
    SWITCH ( 
        TRUE(),
        CurrentType_ = "Corporate" && CurrentGroup_ = "Intune MDM User", "FLW",
        CurrentType_ = "Corporate" && CurrentGroup_ <> "Intune MDM User", CurrentGroup_,
        CurrentType_ = "Personal", "NONE"
    )

Best Regards,

Icey

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

[Anonymous]

This is the result AFTER removing irrelevant columns from the 2 tables used for this.

[Icey]

Hi @Anonymous ,

I thought about it again. How about just using this measure and don't use the original measure for visual filter? Because this expression, "CurrentType_ = "Corporate" && CurrentGroup_ = "Intune MDM User", "FLW""  has no effect when using the visual filter at the same time.

Measure = 
VAR CurrentGroup_ =
    SELECTEDVALUE ( 'Table'[Group] )
VAR CurrentType_ = 
    SELECTEDVALUE ( 'Table'[Owner Type] )
RETURN 
    SWITCH ( 
        TRUE(),
        CurrentType_ = "Corporate" && CurrentGroup_ <> "Intune MDM User", CurrentGroup_,
        CurrentType_ = "Personal", "NONE"
    )

Best Regards,

Icey

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

[Anonymous]

I tried your new measure with and without the Method 2 AD Measure.  I removed the (Personal=NONE) and (if Corporate=IntuneMDMUsers change to FLW) switches one at a time and kept your original. Whenever I use that measure, it's either not enough memory or resources are exceeded. ONLY the Method 2 measure seems to work.

[Anonymous]

When I use Method 2 below:

AD Group Measure =

VAR GroupCount_ =

CALCULATE (

DISTINCTCOUNT ( 'AD2'[Device Config Group] ),

ALLEXCEPT ( 'AD2', 'AD2'[Name] )

)

VAR CurrentGroup_ =

SELECTEDVALUE ( 'AD2'[Device Config Group] )

RETURN

IF ( GroupCount_ = 2 && CurrentGroup_ = "Intune MDM User", 0, 1 )

AND I add your new filter, it fails with the following error:

I then remove Method 2, and it still fails with the Resources Exceeded erro below..

[Anonymous]

If you cannot resolve with current measure, could you split the measure into 2 measures ?  Oner for Peronal = NONE and the other if Corporate is NOT in 2 AD groups display FLW.

[Anonymous]

Any thoughts or ideas ?

Could you either help with the resources exceeded or not enough memory errors OR spilt the measures where:

1. If Owner Type=Personal then AD Group=NONE. 
2. The other is Owner Type=Corporate and belongs to ONLY 1 AD Group called Intune MDM User, then display FLW. 
3. If Owner Type=Corporate and belongs to 2 AD groups, then display FLW, Office Worker or Exec; not Intune MDM Users ( Method 2 measure...which works now)

[Anonymous]

The following error occurs after a very long time of applying the above measure.  I also disabled the very first measure you provided (Method 2).

[Anonymous]

Error appeared.

Your new Measure looked promising UNTIL error appeared.

Any insight ?

Error log is over 85,000 charcters or 34 pages in Word.  Do you need toview the log ?  Could I email it ?