Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Create Measure not Affected by Filter

  Hi,   I have created a measure that outputs the ratio of the number of hours worked to the number of guests in a hotel location. I would like the create a second measure that performs the same c...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you for the response Greg. As you said, I need to provide you with much more information, especially since I am still very new to DAX and don't yet speak the lanuage. I put together a data set for you, which outlines the different tables and will help explain what I am trying to do; although it looks like I can not upload files. I will send you a PM with a drop box link to an excel file. I also think your initial thought on the table not being filtered is correct. Putting an ALL() around that table resulted in an error - I am still learning how Calculate, All and Filter interact with each other and what they expect. 

 

To better explain my goal: I want to create a ratio of employee hours to the number of guests in house (dogs) and I want there to be two versions of this ratio: 

 

  1. A version that can be filtered based on the location "LocationInformation [LocationName]" and calendar date "Calendar"
  2. A version that can only be filtered based on the calendar date, meaning it calculates all locations together as one number for the company. Basically I do not want the output of the code that I pasted to change when I filter locations. 

 

Any filtering will be done via slicer. I tried the formula in your prior post and it threw an error related to the Divide syntax (which I do not believe to be the case and I could not figure out how to fix it). 

 

The formula I posted does work for bullet point #1 (reposted below, note that the filter variables have been changed to match the data I am providing). In a perfect world I would add an AllExcept (LocationInformation) in the Filter arguments but that ends in an error. 

 

Relationships: 

Category 1:1 vDSPDdaily_PowerBI

LocationInformation *:1 vDSPDdaily_PowerBI

Calendar 1:* vDSPDdaily_PowerBI

Labor *:1 LocationInformation

Labor *:1 Calendar

 

Thanks for the help. Please let me know if I can provide anything else. 

  

Hour_Dog_Ratio_Location = divide(
                                Calculate(sum(Labor[DailyLabor]),filter(Labor,Labor[Labor_report_description]="Total Paid (D + I)")),
                                Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound]),
                                   filter(Category,Category[CategoryLabel]="Boarding pets"
                                        ||Category[CategoryLabel]="Daycare pets"
                                        ||Category[CategoryLabel]="Grooming pets"
                                        ||Category[CategoryLabel]="Special services pets"
                                        ||Category[CategoryLabel]="Training pets"))

 

 

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Somail,

 

I think you could use ALLEXCEPT to remove all the filters applied to the datatable except the filters on the location "LocationInformation [LocationName]" and calendar date "Calendar". And then use the filters for [Category] in CALCULATE to get what you want. 

 

It should be something like:

CALCULATE(SUM(vDSPDdaily_PowerBI[DailyValueRound]),
ALLEXCEPT(vDSPDdaily_PowerBI,LocationInformation [LocationName],Calendar[Date]),
Category[CategoryLabel]="Boarding pets" ||Category[CategoryLabel]="Daycare pets" ||Category[CategoryLabel]="Grooming pets" ||Category[CategoryLabel]="Special services pets" ||Category[CategoryLabel]="Training pets")