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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

Really difficult to say without sample data and sample output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

But perhaps try something like:

Hour_Guest_Ratio_Company = 
VAR __laborTable = ALL('Labor')
VAR __categoryTable = ALL('Category')
VAR __numerator = SUMX(FILTER(__laborTable,[Labor_report_description]="Total Paid (D + I)"),[DailyLabor])
VAR __denominator = SUMX(FILTER(__categoryTable,
   [CategoryLabel]="Guests in House"
   ||[CategoryLabel]="Guests in Transit"
   ||[CategoryLabel]="Guests in Limbo"),[DailyValueRound]
)
DIVIDE(__numerator,__denominator,0)

However I am guessing that the problem lies with the portion in red:

 

Hour_Guest_Ratio_Company = divide( Calculate(sum(Labor[DailyLabor]),filter(ALL(Labor),Labor[Labor_report_description]="Total Paid (D + I)")), Calculate(sum(vDSPDdaily_PowerBI[DailyValueRound] ), filter(ALL(Category),Category[CategoryLabel]="Guests in House" ||Category[CategoryLabel]="Guests in Transit" ||Category[CategoryLabel]="Guests in Limbo")))

That seems to be another table where you are not placing an ALL on it so it may be filtered somehow. Again, super difficult without data and knowing what the results should be. 

  • 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")