Dynamic GROUPBY in DAX, PowerBI

1
votes

I'm new to PowerBI and DAX language.

Considering the following table All interactions (which is a collection of user interaction with the system): enter image description here

I'd like to create the following visualization: (On the X-axis is total interactions count, on the Y-axis percentage of users in the system that has that number of interactions)

This can be done by creating a dynamic table like:

TableFoo =
GROUPBY (
    'All interactions',
    'All interactions'[user_name],
    "total_interactions", COUNTX ( CURRENTGROUP (), 'All interactions'[user_name] )
)

Which can be visualized as expected: enter image description here

The problem with that I cannot filter the data dynamically. For example when trying to narrow down the results by selecting certain type or user_details.gender all the grouped data remains static.

How can I approach that?

Thanks.

2
powerbidax

2 Answers

2
votes

The key to making things dynamic is to use measures instead of calculated columns or calculated tables (unless these are calculated within your measure).

I'd suggest creating a separate table to use as your x-axis.

X-axis = GENERATESERIES ( 1, COUNTROWS ( 'All Interactions' ) )

(This is bigger than you need, but should work for the purpose.)

Put 'X-axis'[Value] on your chart Axis and then define a measure to use for the Value field.

Contact Distribution = 
VAR Interactions = SELECTEDVALUE ( 'X-axis'[Value] )
VAR Summary =
    GROUPBY (
        'All interactions',
        'All interactions'[user_name],
        "total_interactions", COUNTX ( CURRENTGROUP (), 'All interactions'[user_name] )
    )
RETURN
    COUNTROWS ( FILTER ( Summary, [total_interactions] = Interactions ) )

This reads the value on the x-axis and then counts the number of users that have that many interactions in your GROUPBY summary table.

This dynamic now since the summary table is calculated inside a measure and can be responsive to filtering, unlike a fixed calculated table.

0
votes

You have to specify in the All function that you want to remove filter on the column Username only. You don't need to use groupby function You can try this formula : ( the format function force to see the decimal)

   format(
     Divide(
        countx(Sheet3,Sheet3[username]),
        Calculate(COUNTROWS(Sheet3),ALL(Sheet3[username])
        )
       ,0)
      , "PERCENT" )