cancel
Showing results for
Did you mean:
Regular Visitor

## dax syntax

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:

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]

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
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
var
var
average_weight_prev_days = CALCULATE(AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Event 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
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],
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])

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

Tks,

Idan

8 REPLIES 8
Community Support Team

## Re: dax syntax

Hi @DAN1980

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

Please make sure the confidential info has been removed before.

We’d like to provide further support afterwards.

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Regular Visitor

## Re: dax syntax

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.

Idan

@v-diye-msft wrote:

Hi @DAN1980

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

Please make sure the confidential info has been removed before.

We’d like to provide further support afterwards.

Best regards,

Dina Ye

Member

## Re: dax syntax

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

Regular Visitor

Tks,

Idan

Member

## Re: dax syntax

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?

Regular Visitor

## Re: dax syntax

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

Regular Visitor

## Re: dax syntax

Hi Dina,

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

Tks,

Idan

Regular Visitor

## Re: dax syntax

Hi All,

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

Tks,

Idan

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!