problem of dragging my measure on x-axis

[Anonymous]

Hi everyone,

I need to visualize the histogram in PowerBI, so I would like to use column chart.

But my measure can't be dragged on x-axis, which I want.

Do you have an idea how to do?

To understand easliy, I will post my measure.

Timeline Events After Opportunity Line Item Created =

VAR EarliestOpportunityLineItemCreated =

    CALCULATE(

        MIN('DIM_Event_Timeline'[Date and Timestamp]),

        FILTER(

            'DIM_Event_Timeline',

            'DIM_Event_Timeline'[Timeline Event] = "Opportunity Line Item Created"

        )

    )

    VAR countEvents =

    CALCULATE(

            COUNTROWS(

                SUMMARIZE(

                    FILTER(

                        'DIM_Event_Timeline',

                        'DIM_Event_Timeline'[Date and Timestamp] > EarliestOpportunityLineItemCreated &&

                        'DIM_Event_Timeline'[Timeline Event] <> "Opportunity Line Item Created"

                    ),

                    'DIM_Interactions'[Content Name],

                    'DIM_Event_Timeline'[Timeline Event]

                )

            )

        )

RETURN

    SWITCH(EarliestOpportunityLineItemCreated,

        BLANK(), BLANK(),

        SWITCH(

            countEvents,

            BLANK(), 0,

            countEvents

        )

    )

Thanks in advance!

[Anonymous]

Hi @Anonymous ,

If what you are creating a measure [Timeline Events After Opportunity Line Item Created], then it can't be put on X-axis of column chart. The X-axis of a column chart is reserved for categorical data, such as product names, dates, or regions. Measures are used to aggregate numerical data, such as sales figures or customer counts, and are typically displayed on the Y-axis of a chart. If you want to display a measure on a column chart, you can use it as the value of the chart, which will be displayed on the Y-axis.

Could you please provide some raw data in your tables 'DIM_Interactions' and 'DIM_Event_Timeline' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi Rena,

I can provide you with the tables, the relationships between them, and the expected result.

I cannot upload all the data columns here, and also I am not an expert in data modeling. Therefore, the tables I provide might be limited and unprofessional. However, I will do my best to explain my data and situation.

1. Tables

DIM_Event_Timeline

Timeline_Event	Timeline_Category
A	1
A	2
A	3
B	1
B	2
B	3
B	3
C	2
C	1

DIM_Interactions

Content Name

apple

banana

kiwi

peach

melon

mango

strawberry

watermelon

lemon

DIM_Contacts with Contact Campaign Involvement

E-Mail	Campaign_ID (CID)
adfafw@gmail.com	A1
asdf234@gmail.com	C1
grtw2iu@gmail.com	B2
ergear2@naver.com	B2
hrtowuhfah@naver.com	B1
uwfhuy5@fau.de	C2
agerhbiub@google.com	A2
htrudf3@google.com	A1
dguhbvu47y@powerbi.com	C1

2. Relationship between tables

DIM_Event_Timeline : DIM_Interactions = * : 1 (key: INTERACTION _UID)

DIM_Event_Timeline : DIM_Contacts with Contact Campaign Involvement = * : * (key: CONTACT_UID)

3. The measures I created

Timeline Events Before Opportunity Line Item Created =

VAR EarliestOpportunityLineItemCreated =

    CALCULATE(

        MIN('DIM_Event_Timeline'[Date and Timestamp]),

        FILTER(

            'DIM_Event_Timeline',

            'DIM_Event_Timeline'[Timeline Event] = "Opportunity Line Item Created"

        )

    )

    VAR countEvents =

    CALCULATE(

        COUNTROWS(

            SUMMARIZE(

                FILTER(

                    'DIM_Event_Timeline',

                    'DIM_Event_Timeline'[Date and Timestamp] < EarliestOpportunityLineItemCreated &&

                    'DIM_Event_Timeline'[Timeline Event] <> "Opportunity Line Item Created"),

                'DIM_Interactions'[Content Name],

                'DIM_Event_Timeline'[Timeline Event]

            )

        )

    )

RETURN

    SWITCH(EarliestOpportunityLineItemCreated,

        BLANK(), BLANK(),

        SWITCH(

            countEvents,

            BLANK(), 0,

            countEvents

        )

    )

Timeline Events After Opportunity Line Item Created =

VAR EarliestOpportunityLineItemCreated =

    CALCULATE(

        MIN('DIM_Event_Timeline'[Date and Timestamp]),

        FILTER(

            'DIM_Event_Timeline',

            'DIM_Event_Timeline'[Timeline Event] = "Opportunity Line Item Created"

        )

    )

    VAR countEvents =

    CALCULATE(

            COUNTROWS(

                SUMMARIZE(

                    FILTER(

                        'DIM_Event_Timeline',

                        'DIM_Event_Timeline'[Date and Timestamp] > EarliestOpportunityLineItemCreated &&

                        'DIM_Event_Timeline'[Timeline Event] <> "Opportunity Line Item Created"

                    ),

                    'DIM_Interactions'[Content Name],

                    'DIM_Event_Timeline'[Timeline Event]

                )

            )

        )

RETURN

    SWITCH(EarliestOpportunityLineItemCreated,

        BLANK(), BLANK(),

        SWITCH(

            countEvents,

            BLANK(), 0,

            countEvents

        )

    )

I created these two measures in DIM_Event_Timeline

4. expected result

The histogram what I wants to visualize is this histogram which contains the number of Touchpoint (TP) on the x-axis (the measures I created) and the count of email on the y-axis.

I also would like to use slicer (for example, Campaign_ID (CID)) to filter the data.

TP = Touchpoint = the measures I created

Thank you in adcanve for your assistance.

Hyeeun