dax syntax

[Anonymous]

Hi All,
I've an issue and i didn't find any solution.

So I hope the smart guys in this community will be able to help me.

I built a system which rank severity of "Event Type"  and "Group" which call on Dashboard:
"Event Type Rank By Severity" and "Group Rank By Severity" (see fig 1)

fig 1:

Issue: 

when I press on group 'r' I didn't receive the Event Type Rank by severity which belong to r group. (see fig 2)

fig 2:

Dashboard link

More info:
Topic:  "Event Type Rank By Severity"
main idea: find the most Event severity.
For this we need to :
1.count Event Type in last day compare the average of previous days - bigger change the severity rank will be higher.
   variable: sheet1[Diff Event Type Last Day Compare Average previous days].

2. percentage from population last day compare to previous days - weight of license last day from all population is
   bigger than the average weight previous days the rank will be higher  and is weight in the population is higher

   variable: sheet1[Diff License Last Day compare Average previous days].

3.rank Event Type =
sheet1[Diff Event Type Last Day Compare Average previous days] *

sheet1[Diff License Last Day compare Average previous days]

Topic:  "Group Rank By Severity"

main idea: find the most Group severity.

1.count Event Type per group in last day compare  the average of previous days 

  variable: sheet1[Diff Events Type Last Day By Group compare Average previous days]

2.percentage from population last day compare to previous days 

 variable: sheet1[Diff License Last day by group compare average previous days]

3.rank Group  = 

sheet1[Diff Events Type Last Day By Group compare Average previous days] * 

sheet1[Diff License Last day by group compare average previous days]

Dashboard link

Formulas:

-----------------------------------------------------------------------------------------------------------------------------

Diff Event Type Last Day compare Average previous days =

var

last_date =CALCULATE(LASTDATE(sheet1[Event Date]),ALL(Sheet1))

var

total_event_type_by_category_last_day = CALCULATE(COUNT(Sheet1[Event Type]), ALLEXCEPT(Sheet1,Sheet1[Event Type],Sheet1[Group]), Sheet1[Event Date]=last_date)

var

previous_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1

var

total_vehicles_per_category_prev_days = CALCULATE( COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Event Type]),Sheet1[Group],Sheet1[Event Date]<=previous_last_date)

var

total_vehicles_population_prev_days =CALCULATE( COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Event Date]))

var

average_weight_prev_days = CALCULATE(AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Event Date],Sheet1[Event Type],

"Events_by_category",CALCULATE(COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Event Type],Sheet1[Group],Sheet1[Event Date])),

"Total_events_in_population", CALCULATE(COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Event Date],Sheet1[Group]))),[Events_by_category]),Sheet1[Event Date]<=previous_last_date)

return

if(ISBLANK(total_event_type_by_category_last_day ),0,total_event_type_by_category_last_day - average_weight_prev_days )

-------------------------------------------------------------------------------------------------------------------------------

Diff License Last day compare Average previous days =

var

last_date =CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))

var

total_vehicles_by_category_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Type]), Sheet1[Event Date]=last_date )

var

total_vehicles_population_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),all(Sheet1),Sheet1[Event Date]=last_date )

var

average_last_date = total_vehicles_by_category_last_day/total_vehicles_population_last_day

var

previous_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1

var

total_vehicles_per_category_prev_days = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Type],Sheet1[Event Date]))

var

total_vehicles_population_prev_days = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Date]))

var

average_weight_prev_days = CALCULATE(AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Event Date],

"vehicles_by_category",CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Type],Sheet1[Event Date])),

"total_vehicles_population", CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Date]))),[vehicles_by_category]/[total_vehicles_population]),Sheet1[Event Date]<=previous_last_date)

return

if(ISBLANK(average_last_date),0,average_last_date - average_weight_prev_days)

---------------------------------------------------------------------------------------------------------------------------

Rank Event Type =

if([Diff Event Type Last Day compare Average previous days]<0,0,[Diff Event Type Last Day compare Average previous days]*[Diff License Last day compare Average previous days])

-----------------------------------------------------------------------------------------------------------------------------

Diff Events Type Last Day By Group compare Average previous days =

var

last_date =CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))

var

total_event_by_client_last_day = CALCULATE(COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Group]), Sheet1[Event Date]=last_date)

var

previous_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1

var

average_weight_prev_days = CALCULATE(AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Event Date],Sheet1[Group],

"Events_by_client",CALCULATE(COUNT(Sheet1[Group]),ALLEXCEPT(Sheet1,Sheet1[Event Date],Sheet1[Group]))),[Events_by_client]),Sheet1[Event Date]<=previous_last_date)

return

if(isblank(total_event_by_client_last_day),0,total_event_by_client_last_day - average_weight_prev_days )

-------------------------------------------------------------------------------------------------------------------------

Diff License Last day by group compare average previous days =

var

last_date =CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))

var

total_vehicles_by_client_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Group]), Sheet1[Event Date]=last_date )

var

total_vehicles_population_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALL(Sheet1),Sheet1[Event Date]=last_date )

var

average_last_date = total_vehicles_by_client_last_day/total_vehicles_population_last_day

var

previous_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1

var

average_weight_prev_days = CALCULATE(AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Event Date],Sheet1[Group],

"vehicles_by_client",CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[License],Sheet1[Event Date])),

"total_vehicles_population", CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Date]))),[vehicles_by_client]/[total_vehicles_population]),Sheet1[Event Date]<=previous_last_date)

return

if(ISBLANK(average_last_date),0,average_last_date - average_weight_prev_days)

-------------------------------------------------------------------------------------------------------------------------------

Rank Group =

if([Diff Events Type Last Day By Group compare Average previous days]<=0,0,[Diff Events Type Last Day By Group compare Average previous days]*[Diff License Last day by group compare average previous days])

--------------------------------------------------------------------------------------------------------------------------------

Dashboard link

Tks,

Idan

[v-diye-msft]

Hi @Anonymous 

If you pinned the visual one by one rather than pin live page on the dashboard, the slicer won’t work.

If not, your dashboard link is not available, could you please upload your pbix in the Onedrive and share us the file link here?

Please make sure the confidential info has been removed before.

We’d like to provide further support afterwards.

Best regards,

Dina Ye

[Anonymous]

Hi Dina,
please see pbix file - link 

the problem is when I filter by 'group' variable or by 'event type' variable

the calculation results are wrong.

I expect to see synchronize between Group and event type.

when I will filter by Group A all the calculation will show according my selection

Same as when I will filter by event type all the calculation will show according my selection.

file contain 
1. summary will give example of the issue

2. group calc

3. event type calc

Hope for your respond.

Tks,

Idan

[Anonymous]

Hi All,
please see pbix file - link 

the problem is when I filter by 'group' variable or by 'event type' variable

the calculation results are wrong.

I expect to see synchronize between Group and event type.

when I will filter by Group A all the calculation will show according my selection

Same as when I will filter by event type all the calculation will show according my selection.

file contain 
1. summary will give example of the issue

2. group calc

3. event type calc

Hope for your respond.

Tks,

Idan

@v-diye-msft @Iamnvt 

[Anonymous]

Hi Dina,

pls see the attch again - link

pin live page unrelevant in this case because the issue is in desktop edition.

when I am pressing on r group I am expecting that all dashboard will filter according r population.

Therefore event type rank by severity should relate to this population only.

 pls explain me how can I upload pbix and i will do it. 

---

thanks for you suoport.

@v-diye-msft 

Idan

---

@v-diye-msft wrote:

Hi @Anonymous 

If you pinned the visual one by one rather than pin live page on the dashboard, the slicer won’t work.

If not, your dashboard link is not available, could you please upload your pbix in the Onedrive and share us the file link here?

Please make sure the confidential info has been removed before.

We’d like to provide further support afterwards.

Best regards,

Dina Ye

---

[Iamnvt]

plesae use Onedrive or Dropbox, Googledrive to upload the PBI file.

The link you gave is not able to shared publicly. just the file is needed.

[Anonymous]

please see the link attach - link

Tks,

Idan

[Iamnvt]

hi,

the visual filter in Filter Pane is filtering [Rank Event Type] > 0. If I remove it, then it remains with the below table:

I guess the 0 in [Rank Event Type] is not what you want. So what is the expected result of that measure?

[Anonymous]

The issue didn't solve.

The filter 0 isn't the problem.
Today I will upload the issue in a more understandable form

Tks,

Idan