Calendar table relationships problem

[Syndicate_Admin]

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:

I also put the relationships of the model:

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!

[v-yiruan-msft]

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]

Hello @Syndicate_Admin,

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

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!

[v-yiruan-msft · Accepted solution]

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

[David-Ganor]

Hi @Syndicate_Admin ,

Does the "Date" axis on the chart "comes" from the date table?

Thanks,

David