dax syntax

Question

Hi All,I've an issue and i didn't find any solution.So I hope the smart guys in this community&nbsp;will be able to help me.&nbsp;I built a system which rank severity of "Event Type"&nbsp; and "Group" which&nbsp;call on Dashboard:"Event Type Rank By Severity" and "Group Rank By Severity" (see fig 1)fig 1:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Issue:&nbsp; when I press on group 'r' I didn't receive the Event Type Rank by severity which belong to r group. (see fig 2)&nbsp;fig 2:Dashboard link&nbsp;More info:Topic:&nbsp; "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.&nbsp; &nbsp;variable:&nbsp;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&nbsp; &nbsp;bigger than the average weight previous days the rank will be higher&nbsp; and is weight in the population is higher&nbsp; &nbsp;variable:&nbsp;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]&nbsp;Topic:&nbsp; "Group Rank By Severity"main idea: find the most Group severity.1.count Event Type per group in last day compare&nbsp; the average of previous days&nbsp;&nbsp; variable: sheet1[Diff Events Type Last Day By Group compare Average previous days]2.percentage from population last day compare to previous days&nbsp;&nbsp;variable: sheet1[Diff License Last day by group compare average previous days]3.rank Group&nbsp; =&nbsp;sheet1[Diff Events Type Last Day By Group compare Average previous days] *&nbsp;sheet1[Diff License Last day by group compare average previous days]&nbsp;Dashboard link&nbsp;Formulas:-----------------------------------------------------------------------------------------------------------------------------Diff Event Type Last Day compare Average previous days = varlast_date =CALCULATE(LASTDATE(sheet1[Event Date]),ALL(Sheet1))vartotal_event_type_by_category_last_day = CALCULATE(COUNT(Sheet1[Event Type]), ALLEXCEPT(Sheet1,Sheet1[Event Type],Sheet1[Group]), Sheet1[Event Date]=last_date)varprevious_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1vartotal_vehicles_per_category_prev_days = CALCULATE( COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Event Type]),Sheet1[Group],Sheet1[Event Date]&lt;=previous_last_date)vartotal_vehicles_population_prev_days =CALCULATE( COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Event Date]))varaverage_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]&lt;=previous_last_date)returnif(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 = varlast_date =CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))vartotal_vehicles_by_category_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Type]), Sheet1[Event Date]=last_date )vartotal_vehicles_population_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),all(Sheet1),Sheet1[Event Date]=last_date )varaverage_last_date = total_vehicles_by_category_last_day/total_vehicles_population_last_dayvarprevious_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1vartotal_vehicles_per_category_prev_days = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Type],Sheet1[Event Date]))vartotal_vehicles_population_prev_days = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Event Date]))varaverage_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]&lt;=previous_last_date)returnif(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]&lt;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 = varlast_date =CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))vartotal_event_by_client_last_day = CALCULATE(COUNT(Sheet1[Event Type]),ALLEXCEPT(Sheet1,Sheet1[Group]), Sheet1[Event Date]=last_date)varprevious_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1varaverage_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]&lt;=previous_last_date)returnif(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 = varlast_date =CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))vartotal_vehicles_by_client_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALLEXCEPT(Sheet1,Sheet1[Group]), Sheet1[Event Date]=last_date )vartotal_vehicles_population_last_day = CALCULATE(DISTINCTCOUNT(Sheet1[License]),ALL(Sheet1),Sheet1[Event Date]=last_date )varaverage_last_date = total_vehicles_by_client_last_day/total_vehicles_population_last_dayvarprevious_last_date = CALCULATE(LASTDATE(Sheet1[Event Date]),ALL(Sheet1))-1varaverage_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]&lt;=previous_last_date)returnif(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]&lt;=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&nbsp;Tks,Idan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-diye-msft · Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;If you pinned the visual one by one rather than pin live page on the dashboard, the slicer won’t work.&nbsp;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.&nbsp;We’d like to provide further support afterwards.&nbsp;Best regards,Dina Ye

anonymous · Answer

Hi Dina,pls see the attch again - link&nbsp;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.&nbsp;pls explain me how can I upload pbix and i will do it.&nbsp;thanks for you suoport.v-diye-msft&nbsp;Idanv-diye-msft&nbsp;wrote:Hi&nbsp;Anonymous&nbsp;&nbsp;If you pinned the visual one by one rather than pin live page on the dashboard, the slicer won’t work.&nbsp;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.&nbsp;We’d like to provide further support afterwards.&nbsp;Best regards,Dina Ye

iamnvt · Answer

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 · Answer

please see the link attach - link&nbsp;Tks,Idan

iamnvt · Answer

hi,&nbsp;the visual filter in Filter Pane is filtering [Rank Event Type] &gt; 0. If I remove it, then it remains with the below table:&nbsp;I guess the 0 in [Rank Event Type] is not what you want. So what is the expected result of that measure?&nbsp;&nbsp;

Forum Discussion

dax syntax

8 Replies

Related content

Syntax problem

DAX syntax highlighting in Notepad++

DAX syntax error for ...

SQL Query Syntax issue

DAX syntax