cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DAN1980 Regular Visitor
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:

from all report.JPG

 

 

 

 

 

 

 

 

 

 

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:

r group only.JPG

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

 

 

 

 

 

 

 

 

 

 

 

 

 

8 REPLIES 8
Community Support Team
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.

 

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

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.
DAN1980 Regular Visitor
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.

@v-diye-msft 

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.

 

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 Member
Member

Re: dax syntax

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.

DAN1980 Regular Visitor
Regular Visitor

Re: dax syntax

please see the link attach - link

 

Tks,

Idan

Iamnvt Member
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:

Capture.PNG

 

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

 

 

DAN1980 Regular Visitor
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

DAN1980 Regular Visitor
Regular Visitor

Re: dax syntax

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

 

 

DAN1980 Regular Visitor
Regular Visitor

Re: dax syntax

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 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors