Forum Discussion

Connor_S's avatar
Connor_S
New Member
4 years ago

NEW TO POWER BI - Creating calculated measures using date and categorical variables

Hello community!

 

I am trying to create a rate calculated measure with a date field and a categorical field. I am needing to count a specific category from my categorical variable and count a date from my date variable to create my rate.

 

rate = (date variable / specific category from categorical variable)

 

I have tried counting specific instances in my measure formula, but have been unsuccessful. Does anybody know if this can be done or how it can be done?

 

I have also tried to create calculated columns from these specific fields, but I get an error code saying it wont work because I am using DirectQuery mode.

Need Help
Tutorial Requests

4 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Connor_S ,

     

    For the direct query schema, the created calculated column cannot refer to columns from other tables.

    Then, you can try similiar formula:

    M =
VAR date_ =
    MAX ( DimDate[DateKey] )
RETURN
    DIVIDE ( date_, 'table'[category], BLANK () )

    Also, I am confused about the logic of the formula calculation, how does the date field divide with a specific category? What are its specific requirements?


    If the problem is still not resolved, please provide screenshots of the desired results with descriptions and information such as test data.


    Best Regards,
    Henry


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

  • Connor_S's avatar
    Connor_S
    New Member
    4 years ago

    Henry,

     

    Thank you for your response. I appreciate you providing the information about calculated columns and the limitations they have. To minimize confusion regarding my formula, I'll try to clarify. I am calculating a close rate. the two variables that I need to use reside in two different tables and neither are ideal data types for calculations. I am trying to divide a count of contracts signed by a specific category in a categorical variable. My signed contract field in table A is a date type. When a contract is signed I need to count it. My second variable in table B is a categorical variable and text type with 10 categories.vThis variable is based on the outcome of a meeting. if the meeting occurred, I need to capture that outcome.

     

     

    I am wanting to count every date a contract was signed and divide that count by a count of every discovery outcome that is equal to "Had Discovery". I am not sure if I can do this with these table formats. My strategy was to create calculated measures in each repective table and then try to divide the calculated measures by each other. I received errors relative to being in direct query mode. I hope this clarifies the situation better. Thank you!

  • croberts21's avatar
    croberts21
    Icon for Continued Contributor rankContinued Contributor
    4 years ago

    We find that Import mode, not DirectQuery, meets more of our needs. There are pros and cons to both Import and DQ mode. This might help you. https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

    And in Import mode, you can have data update automatically every day if you want, by setting up a Data Gateway. See this: https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

    Even the Data Gateway has different modes. Here is more info on the Personal Mode: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-personal-mode