Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Calendar table relationships problem

Good morning

I tell you:

I need to make a line chart with 3 measurements.

Opp GANADAS = CALCULATE(COUNT(opportunity[opportunityid]), FILTER(opportunity, opportunity[statecodename] = "Ganada"),


USERELATIONSHIP(opportunity[estimatedclosedate],'CALENDAR'[Date]))

Opp PERDIDAS = CALCULATE(COUNT(opportunity[opportunityid]), FILTER(opportunity, opportunity[statecodename] = "Perdida"),


USERELATIONSHIP(opportunity[estimatedclosedate],'CALENDAR'[Date]))

Opp CUALIF = CALCULATE(count(opportunity[opportunityid]), FILTER(opportunity, opportunity[me_faseahoraname] = "3. Evaluación" || opportunity[me_faseahoraname] = "4. Negociación" || opportunity[me_faseahoraname] = "5. Cierre"), FILTER(opportunity,opportunity[me_typename] = "Nueva"), FILTER(opportunity, opportunity[statecodename] = "Abierta"),


USERELATIONSHIP('CALENDAR'[Date], opportunity[me_createdon]))
The Opp WON and the Opp LOST I have to reflect with estimatedclosedate and the Opp CUALIF with createdon. For this reason I use USERELATIONSHIP. But in the graph it only shows me the two measures that are with the estimatedclosedate date:
mariases94_0-1644586484590.png

I also put the relationships of the model:

mariases94_1-1644586516899.png

How can I make opp CUALIF show up in the same graph as the other two measures? I do not know how to make these data with a different date are shown in the same graph.

Thank you very much in advance and greetings!

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi @mariases94 ,

    Please delete any relationship between CALENDAR table and opportunity table first. Then update the formula of measure [Opp GANADAS], [Opp PERDIDAS] and [OppCUALIF ] as below:

    Opp GANADAS =
    CALCULATE (
        COUNT ( opportunity[opportunityid] ),
        FILTER (
            opportunity,
            opportunity[statecodename] = "Ganada"
                && opportunity[estimatedclosedate] = SELECTEDVALUE ( 'CALENDAR'[Date] )
        )
    )
    Opp PERDIDAS =
    CALCULATE (
        COUNT ( opportunity[opportunityid] ),
        FILTER (
            opportunity,
            opportunity[statecodename] = "Perdida"
                && opportunity[estimatedclosedate] = SELECTEDVALUE ( 'CALENDAR'[Date] )
        )
    )
    OppCUALIF =
    CALCULATE (
        COUNT ( opportunity[opportunityid] ),
        FILTER (
            opportunity,
            opportunity[me_faseahoraname]
                IN { "3. Evaluación", "4. Negociación", "5. Cierre" }
                    && opportunity[me_typename] = "Nueva"
                    && opportunity[statecodename] = "Abierta"
                    && opportunity[estimatedclosedate] = SELECTEDVALUE ( 'CALENDAR'[Date] )
        )
    )

    If the above one still can't help you get the desired result, please share a simplified pbix file (only include opportunity and calendar table without sensitive data) with me in order to make troubleshooting. Thank you.

    Best Regards

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi mariases94 ,

    Please check if there is data in your data table opportunity that also meets the following conditions.
    1. me_faseahoraname is "3. Evaluación", "4. Negociación" or "5. Cierre”
    2. me_typename is Nueva
    3. statecodename is Abierta
    4.  me_createdon exist the data from January 2021 to November 2022
    Does the X axis field of the line chart come from the CALENDAR table or the opportunity table? Which field from one of them is applied on the X axis field? By the way, on which columns are the relationships created between the exportunity table and CALENDAR? Thanks.
    By the way, the formula for the measure [OppCUALIF] can be simplified to look like this:

    OppCUALIF =
CALCULATE (
    COUNT ( opportunity[opportunityid] ),
    FILTER (
        opportunity,
        opportunity[me_faseahoraname]
            IN { "3. Evaluación", "4. Negociación", "5. Cierre" }
            && opportunity[me_typename] = "Nueva"
            && opportunity[statecodename] = "Abierta"
    ),
    USERELATIONSHIP ( 'CALENDAR'[Date], opportunity[me_createdon] )
)

    Best Regards

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Hello @Syndicate_Admin,

      Yes, filtering with the parameters you have told me if there is data in the "me_createdon" column:

      mariases94_0-1644940660648.png

      I answer your questions:

      Does the X-axis field of the line chart come from the CALENDAR table or the opportunity table? It comes from the CALENDAR table (specifically the month-year column).

      Which field of one of them is applied in the X-axis field? Month-year

      By the way, in which columns are the relationships between the exportunity table and CALENDAR created? I have related the "estimatedclosedate" column of the Opportunity table to the "Date" field of the Calendar table.

      Thank you very much in advance.

      Greetings!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi @mariases94 ,

        Please delete any relationship between CALENDAR table and opportunity table first. Then update the formula of measure [Opp GANADAS], [Opp PERDIDAS] and [OppCUALIF ] as below:

        Opp GANADAS =
        CALCULATE (
            COUNT ( opportunity[opportunityid] ),
            FILTER (
                opportunity,
                opportunity[statecodename] = "Ganada"
                    && opportunity[estimatedclosedate] = SELECTEDVALUE ( 'CALENDAR'[Date] )
            )
        )
        Opp PERDIDAS =
        CALCULATE (
            COUNT ( opportunity[opportunityid] ),
            FILTER (
                opportunity,
                opportunity[statecodename] = "Perdida"
                    && opportunity[estimatedclosedate] = SELECTEDVALUE ( 'CALENDAR'[Date] )
            )
        )
        OppCUALIF =
        CALCULATE (
            COUNT ( opportunity[opportunityid] ),
            FILTER (
                opportunity,
                opportunity[me_faseahoraname]
                    IN { "3. Evaluación", "4. Negociación", "5. Cierre" }
                        && opportunity[me_typename] = "Nueva"
                        && opportunity[statecodename] = "Abierta"
                        && opportunity[estimatedclosedate] = SELECTEDVALUE ( 'CALENDAR'[Date] )
            )
        )

        If the above one still can't help you get the desired result, please share a simplified pbix file (only include opportunity and calendar table without sensitive data) with me in order to make troubleshooting. Thank you.

        Best Regards