Dividing a Measure into Multiple Categorizations

[Anonymous]

Hi all,

I'm struggling with this graph I'm trying to create, and I could really use some help.

This is what my data looks like (just the upper three tables: Products, Entries and Durations). 

My model looks like this: 

What I'm trying to do is to make a graph of the number of products that have a valid entry in a selected date (range), and categorize this number (of products) using the (valid) durations. By 'valid', I mean that the selected date (range) is in between 'In' and 'Out'(for 'Entries') or in between 'Start' and 'End' (for 'Durations'). Entries never overlap, but Durations can overlap.

For the 'have an entry' measure I use:

have an entry = 
CALCULATE (
    COUNT ( Entries[ID] ),
    FILTER (
        Entries,
        Entries[In] <= MAX ( 'calendar'[Date] )
            && (
                Entries[Out] >= MIN ( 'calendar'[Date] )
            )
    )
)

For the 'have a duration' measure I have:

have a duration = 
CALCULATE (
    COUNT( Products[ID] ),
    FILTER (
        Durations,
        Durations[Start] <= MAX ( 'calendar'[Date] )
            && Durations[End] >= MIN ( 'calendar'[Date] )
            && Durations[Start] <> BLANK ()
    )
)

You can see the results of these measures in the 'Final Result' table in my first screenshot.

But at this point, I'm stuck. 😞

What I need to do is graph the 'have an entry' measure by date and categorize using the Durations table's 'Category'.

The twist is that it can happen that one valid entry has two valid durations. In this example, ProductID = 288 has two valid durations, which includes one with category 'C'. Whenever there is a mix with category 'C' with another one, I want to assign 60% of that entry to category C and 40% of that entry to another (A or B).

The parts with category 'C' should be highlighted in color 'Y'.

The parts with category 'A', 'B' or an unknown category should be highlighted in color 'X'.

In this example the final result in a graph should be:

• If a product has a valid entry and only one (or more) valid duration(s) with category 'C', the entry should be entirely colored in 'Y'.
• If a product has a valid entry and 100 valid durations with categories A and B and only one with category 'C', the entry should still be 60/40 colored in Y/X.
• If a product has a valid entry and no valid durations (so category 'Unknown'), it should be colored 'X'.
• If a product has a valid entry and one valid durations with category A or B, it should be colored 'X'.

... and so forth..

I hope this makes sense! And I really hope one of you is willing to invest the time to help me out on this one. It will be appreciated immensly!

🙃

EDIT: To clarify: I want to end up with one (or two) 'block(s)' in my graph with totals for 'X' and 'Y'. So in the example above there would be a block for 'X' with a total amount of 2.4 and a block for 'Y' with a total amount of 0.6. 'X' + 'Y' is always an integer.

[OwenAuger]

@Anonymous - quick question on the visualization itself.

Assuming you use a stacked column like the one you've shown, do you need the column to be split so that it has up to two components per Product (one color X and one color Y)?

And do you need Color X & Color Y to be grouped by product, so that you would end up with an alternating pattern if each Product had both colours?

Or would you be happy to simply have one block of Color X & one block of Color Y, of appropriate total sizes, but not broken down as such?

Regards,

Owen

[Anonymous]

Hi @OwenAuger,

That's a good one. I see that my sketch could be confusing in that sense, sorry about that.

I would actually be very happy to just have one block of Color X & one block Color Y of appropriate total sizes!

Thanks for paying attention. 🙂 

[OwenAuger · Accepted solution]

@Anonymous  thanks for the clarification 🙂

I would do it this way (PBIX link )

• Create a disconnected table called Color which has a column Color, with values "X" and "Y"
• Create this measure to use in the clustered column chart:

Measure for Visual = 
SUMX ( 
    VALUES ( Products[ID] ),
    IF (
        [have an entry], // True if [have an entry] is nonzero
        VAR HasC = 
            CALCULATE ( [have a duration], Durations[Category] = "C" ) // True if [have a duration] is nonzero when Category = "C"
        RETURN
        SUMX ( 
            Color,
            SWITCH ( 
                Color[Color],
                "X", IF ( HasC, 0.4, 1 ),
                "Y", IF ( HasC, 0.6 )
            )
        )
    )
)​​

• Create a column chart with Color[Color] as the Legend and [Measure for Visual] as the measure. Set the colors for X & Y appropriately.
• Result looks something like this:
  
  
  

Regards 

Owen

[Anonymous]

@OwenAuger 

Amazing, thank you! 🙏 

This DAX programming language looks simpler than it really is. At least, that's how I feel about it.

I'm going to try this out on my actual dataset. I know you answered my question correctly at this point, so please forgive me for letting the topic open for a moment longer while I do some testing. I might try to ask you a little follow-up question, if you're willing to take a look ofcourse.

Will be back!

[Anonymous]

@OwenAuger 

It's working great, thanks again! 😀

I ended up splitting the measure into two separate ones, one for 'X' and one for 'Y'. Like this:

Measure for Visual X = 
SUMX ( 
    VALUES ( Products[ID] ),
    IF (
        [have an entry], // True if [have an entry] is nonzero
        VAR HasC = 
            CALCULATE ( [have a duration], Durations[Category] = "C" ) // True if [have a duration] is nonzero when Category = "C"
        RETURN
             IF ( HasC, 0.4, 1 )
            )
        )
    

&

Measure for Visual Y = 
SUMX ( 
    VALUES ( Products[ID] ),
    IF (
        [have an entry], // True if [have an entry] is nonzero
        VAR HasC = 
            CALCULATE ( [have a duration], Durations[Category] = "C" ) // True if [have a duration] is nonzero when Category = "C"
        RETURN
         IF ( HasC, 0.6 )
            )
        )
    

Which eliminated the need for an extra 'Color' table. 

[Anonymous]

HI @Anonymous 

If ID 288 has three categories just like A, B and C, will the stacked column divid ID =288 to C =60% and A&B =40%?

Best Regards,

Rico Zhou

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

[Anonymous]

@Anonymous 

Yes, correct!

Even if an entry has a million times 'A' or 'B' and just once 'C', that entry should be marked 40% in color 'X' and 60% in color 'Y'.