Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"),
Opp PERDIDAS = CALCULATE(COUNT(opportunity[opportunityid]), FILTER(opportunity, opportunity[statecodename] = "Perdida"),
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"),
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!
Solved! Go to Solution.
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
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
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!
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
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |