Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have to calculate number of days if condition is matched. Please find the below table:
1. Columns name are : Date , brand, Average coverage
2. Average coverage is a calculated metrics and the used formula as (metrics = AVERAGE('Promoter article'[Coverage]) )
3. Now if I select any brand like "Aardee" then for 1st march 2022 to 31st march 2022 - matched and not matched value will count.
Aardee = Matched = 10, not matched = 17
Date | Brand | Avg Coverage | condition |
3/1/2022 | Aardee | 83% | Matched |
3/2/2022 | Aardee | 42% | not matched |
3/3/2022 | Aardee | 92% | Matched |
3/4/2022 | Aardee | 79% | not matched |
3/5/2022 | Aardee | 71% | not matched |
3/6/2022 | Aardee | 71% | not matched |
3/7/2022 | Aardee | 92% | Matched |
3/8/2022 | Aardee | 58% | not matched |
3/10/2022 | Aardee | 92% | Matched |
3/11/2022 | Aardee | 79% | not matched |
3/12/2022 | Aardee | 79% | not matched |
3/13/2022 | Aardee | 29% | not matched |
3/14/2022 | Aardee | 58% | not matched |
3/15/2022 | Aardee | 58% | not matched |
3/17/2022 | Aardee | 58% | not matched |
3/18/2022 | Aardee | 0% | not matched |
3/19/2022 | Aardee | 57% | not matched |
3/20/2022 | Aardee | 50% | not matched |
3/21/2022 | Aardee | 92% | Matched |
3/22/2022 | Aardee | 92% | Matched |
3/24/2022 | Aardee | 92% | Matched |
3/25/2022 | Aardee | 79% | not matched |
3/26/2022 | Aardee | 57% | not matched |
3/27/2022 | Aardee | 71% | not matched |
3/28/2022 | Aardee | 92% | Matched |
3/29/2022 | Aardee | 83% | Matched |
3/31/2022 | Aardee | 92% | Matched |
Thanks
Shipra
Solved! Go to Solution.
Hi @Anonymous ,
Please refer to my pbix file to see if it helps you.
Create a measure.
Measure =
VAR _month =
MONTH ( MAX ( 'Table'[Date] ) )
VAR _year =
YEAR ( MAX ( 'Table'[Date] ) )
RETURN
CALCULATE (
COUNT ( 'Table'[condition] ),
FILTER (
ALL ( 'Table' ),
MONTH ( MAX ( 'Table'[Date] ) ) = _month
&& YEAR ( MAX ( 'Table'[Date] ) ) = _year
&& 'Table'[Brand] = SELECTEDVALUE ( 'Table'[Brand] )
&& 'Table'[condition] = SELECTEDVALUE ( 'Table'[condition] )
)
)
If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please refer to my pbix file to see if it helps you.
Create a measure.
Measure =
VAR _month =
MONTH ( MAX ( 'Table'[Date] ) )
VAR _year =
YEAR ( MAX ( 'Table'[Date] ) )
RETURN
CALCULATE (
COUNT ( 'Table'[condition] ),
FILTER (
ALL ( 'Table' ),
MONTH ( MAX ( 'Table'[Date] ) ) = _month
&& YEAR ( MAX ( 'Table'[Date] ) ) = _year
&& 'Table'[Brand] = SELECTEDVALUE ( 'Table'[Brand] )
&& 'Table'[condition] = SELECTEDVALUE ( 'Table'[condition] )
)
)
If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi:
Please see attached file for hopeful solution. Good luck..
https://drive.google.com/file/d/1O3t8cwNOd2tcM07IZ-oKE2K57setUZdj/view?usp=sharing
Then I used measures to sum the hits under each result.
Perhaps something like...
[Matched Days] =
CALCULATE(
DISTINCTCOUNT( T[Date] ),
KEEPFILTERS(
T[Condition] = "matched"
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |