How to create a measure that flags when one dimension occurs multiple across another dimension

[Don-Bot]

I'm working on an annoying issue.  

So for two dimensions in our model (State and Code) our client wants to see what codes go across multiple states.  I created a matrix visual to show this but since our client can have 500 + codes across 50 states it is rather difficult to see which ones are being shared.  

I then went to see if I could create a measure that I could potentially use as either a filter or a conditional formatting to highlight where things are shared.  However, I couldn't get that to work either.  Below is what I have:

Ticket ID    

a  

b

c

c

d

Ticket Location 

ID    STATE

a       TN

b       CA

C        TX

d        OR

Ticket Code

ID    CODE

a       abc

b       abc

c        def

d        ghi

So I would want a visual to return something like:

ID   CODE STATE  Number of Tickets

a     abc      TN       (Count of IDs)

b     abc     CA         (count of IDs)

I tried the below dax but the state count just returned a count of all states so it didn't work

DistinctStateCount = 
CALCULATE(
    DISTINCTCOUNT('Ticket Location'[State]),
    ALLEXCEPT('Registration', 'Registration'[Code])
)



MultipleStatesFlag = 
IF(
    [DistinctStateCount] > 1,
    1,
    0
)

[Anonymous]

Thank you darkniqht 

Hi, @Don-Bot 

According to your description, I first created a calculated column using the following expression:

Stage = LOOKUPVALUE('Ticket Location'[Location Stage],'Ticket Location'[Ticket ID    ],'Registration'[Ticket ID    ]) 

Next, I created a measure:

Count = CALCULATE(COUNTA(Registration[Stage]),ALLEXCEPT(Registration,'Registration'[Code]))

You can see that it is possible to correctly calculate how many states each code spans.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Don-Bot]

@Anonymous 
When I try this code:

Stage = LOOKUPVALUE('Ticket Location'[State],'Ticket Location'[LocationID],'Registration'[RegistrationID]) 

  (I apologize for having the wrong information above)

I get the message  

[darkniqht]

Here's a simpler approach:

1. Distinct State Count Measure: Modify your measure to accurately count distinct states for each code:

   DAX

DistinctStateCount = 
CALCULATE(
    DISTINCTCOUNT('Ticket Location'[State]),
    VALUES('Ticket Code'[Code])
)​

• Multiple States Flag Measure: Use this measure to flag codes that appear in more than one state:

  DAX

MultipleStatesFlag = 
IF(
    [DistinctStateCount] > 1,
    1,
    0
)

• Use in Visual: You can use these measures in your matrix visual. Add CODE, STATE, and the count of IDs. Apply a filter to show only where MultipleStatesFlag equals 1 to highlight shared codes.

This way, you can easily identify which codes are used across multiple states!

[Don-Bot]

@darkniqht , your solution is extremely close to what I want but I'm getting an error of: The following syntax error occurred during parsing: Invalid token, Line 5, Offset 2, ​. when I try to use it.  

This could be my fault in not explaining the back end with consists of a main fact table, plus a registration code table and then a location table.  The 2 dimensions join to the fact table by ID fields.

I was able to modify  your code to the below which works... but I'm struggling to make sure the "Max" below is doing what I want and not leaving any codes or duplicates out.

DistinctStateCount = 
CALCULATE(
    DISTINCTCOUNT('Ticket Location'[State]),
    FILTER(
        'Ticket',
        'Ticket'[CDC_ID] = MAX('Registration'[CDC_ID])
    )
)