Forum Discussion

Valentin09's avatar
Valentin09
Regular Visitor
1 year ago
Solved

Measure: Day Average with specific requirements

Hi everyone,

 

I need help with the following database:

 

I have got the following formula which works fine: 

DayAverage_Duration =
CALCULATE(AVERAGE(stats_df[total_distance]),
          FILTER(stats_df,(stats_df[activity_participation]= "Full")))
 
In the activity_tag tab there are the following possiblities:
- Training
- Match
- Training_AM
- Training_Aktivierung
- Lizenz
 
Now I want the above formula but without data which is tagged as Training_Aktivierung and Lizenz. 
 
Could somebody help me?
 
Thanks a lot!
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    Hi Valentin09 ,

    Thank you for reaching out to the Microsoft Fabric Community. I tested the solution using a sample dataset that matches your scenario. The DAX formula you were provided works correctly.

    DayAverage_Duration = 
CALCULATE(
    AVERAGE(stats_df[total_distance]),
    FILTER(
        stats_df,
        stats_df[activity_participation] = "Full"
            && NOT stats_df[activity_tag] IN { "Training_Aktivierung", "Lizenz" }
    )
)

     

    Using the sample data

    John Doe → 3100

    Alex Miller → 3000

    The result is (3100 + 3000) /2= 3050, which I verified in Power BI.

     

    FYI:

    Thank you for your response govind_021  & mdaatifraza5556 .

     

     If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

     

4 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Icon for Super User rankSuper User
    1 year ago

    Hi Valentin09 

    Can you please try the below DAX.

    DayAverage_Duration =
    CALCULATE(
    AVERAGE(stats_df[total_distance]),
    FILTER(
    stats_df,
    stats_df[activity_participation] = "Full"
    && NOT stats_df[activity_tag] IN { "Training_Aktivierung", "Lizenz" }
    )
    )



    If this answers your questions, kindly accept it as a solution and give kudos.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Valentin09 ,

    Thank you for reaching out to the Microsoft Fabric Community. I tested the solution using a sample dataset that matches your scenario. The DAX formula you were provided works correctly.

    DayAverage_Duration = 
CALCULATE(
    AVERAGE(stats_df[total_distance]),
    FILTER(
        stats_df,
        stats_df[activity_participation] = "Full"
            && NOT stats_df[activity_tag] IN { "Training_Aktivierung", "Lizenz" }
    )
)

     

    Using the sample data

    John Doe → 3100

    Alex Miller → 3000

    The result is (3100 + 3000) /2= 3050, which I verified in Power BI.

     

    FYI:

    Thank you for your response govind_021  & mdaatifraza5556 .

     

     If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

     

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Valentin09 ,

    Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.