Group by and Dax

Hi All,

I need help in creating an alert mechanism , attached is the pbix file in drive
https://drive.google.com/file/d/1pj6dQrNje6A43zp5U4u1-Oza2ToXzlys/view?usp=sharing

  1. Alert 1 :

Step 1: Identify and group countries where spends are > 200 and leads are > 50 in last six months, this will help us to identify countries with significant data for analysis, Name them as S-Countries.

Step 2: Calculate average CPL(Cost per lead) for S-Countries and compare the same with every specific country. (Current CPL Vs Avg. CPL of S-Countries)

Step 3: If the CPL is >Avg. CPL then Red else Green.

Alert 1 will help us identify countries where CPL is higher than the Average.

  1. Alert 2 :
  2. Step 1: Identify and group countries where spends are > 200 and leads are > 50 in last six months, this will help us to identify countries with significant data for analysis, Name them as S-Countries.
  3. Step 2: Calculate average Lead to App rate for S-Countries and compare the same with every specific country. (Current L2A(Lead to application) Vs Avg. L2A of S-Countries)
  4. Step 3: If the L2A is >Avg. L2A then Green else Red.

Alert 2 will help us identify countries which are converting better.

  1. Alert 3 (Decision) :

If Alert 1 is GREEN and Alert 2 is Green, Ok with those countries.

If Alert 1 is RED and Alert 2 is GREEN, then review these countries because though the lead buy is costlier, they show good signs of conversion.

If Alert 1 and Alert 2 both are RED, then this need to be Stopped

If Alert 1 is GREEN and Alert 2 is RED then review spends in these countries as the leads are cheaper but they are converting

Alert 3 will help us identify countries for better performance.

appreciate if someone can help me here

@Nagi_k,

I tried to access the Google Drive file, but the share privileges did not allow me to do so. Can you please change them to allow anyone with the link to access the file?

Thanks.

– Brian

@BrianJ

Done , could you please check now

@Nagi_k,

Thanks – I got the file, but unfortunately your data model needs a lot of work before I would even think of touching the DAX code. Some of the problems I see:

  1. you are trying to do time intelligence, yet do not have a proper date table at the day-level granularity, which is a necessary condition for the type of time intelligence you want to do. I would very strongly recommend using @Melissa’s Extended Date Table code to build your date table, then you will have to mark this as a date table.
  2. your data model does not come close to conforming to a star schema, for which DAX is optimized. It is hard from your data model diagram to even discern what is a fact table and what is a dimension table. I would organize your data model according to the Collie/waterfall approach where the dimension tables are on top, and “flow downward” to the fact tables via one-to-many relationships.
  3. your data model contains multiple bidirectional relationships, which should be eliminated if at all possible, as these can produce inaccurate and/or difficult to interpret results in DAX due to ambiguous paths between tables.

Here are some videos and articles that should help you organize this data model into a star schema and connect it to a proper date table:

https://portal.enterprisedna.co/courses/best-practices-feature-reviews/lectures/17768631

I hope this is helpful.

– Brian

3 Likes

Hi @Nagi_K, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Nagi_K, we’ve noticed that no response has been received from you since the 16th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi @Nagi_K, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!