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.
Hello,
I am looking for a quick help here.
I have survey data where the survey's are conducted after every few months. There is no fix timeline.
So here is the scenario.
For IT Survey Area, 3 surveys were conducted in (June 2021, October 2021, February 2022)
I have 2 measures that calculates net score for a category of questions.
1st Measure (Current Netscore) with Dax
2nd Measure (Previous Survey) with Dax
I would really appreciate any help here to fix this issue.
Thank you
Hi @JIGAR ,
You can try adding the all() function
IT bench1 Netscore LP % =
VAR max_date = [Max IT survey date]
VAR area = [Survey Area IT]
VAR last_period_date =
CALCULATE (
MAX ( 'Survey attribute'[Survey date] ),
FILTER (
ALL ( 'Survey fact' ),
'Survey attribute'[Survey area] = area
&& 'Survey attribute'[Survey date] < max_date
)
)
VAR top_2_boxes =
CALCULATE (
DISTINCTCOUNT ( 'Survey fact'[Response ID] ),
FILTER (
ALL ( 'Survey fact' ),
'Survey fact'[Survey date] = last_period_date
&& 'Survey fact'[Question ID] = "Bench1"
&& 'Survey fact'[Answer] IN { "4", "5" }
)
)
VAR bottom_2_boxes =
CALCULATE (
DISTINCTCOUNT ( 'Survey fact'[Response ID] ),
FILTER (
ALL ( 'Survey fact' ),
'Survey fact'[Survey date] = last_period_date
&& 'Survey fact'[Question ID] = "Bench1"
&& 'Survey fact'[Answer] IN { "1", "2" }
)
)
VAR denominator =
CALCULATE (
DISTINCTCOUNT ( 'Survey fact'[Response ID] ),
FILTER (
ALL ( 'Survey fact' ),
'Survey fact'[Survey date] = last_period_date
&& 'Survey fact'[Question ID] = "Bench1"
&& 'Survey fact'[Answer] IN { "1", "2", "3", "4", "5" }
)
)
RETURN
DIVIDE ( ( top_2_boxes - bottom_2_boxes ), denominator, 0 ) * 100
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@JIGAR Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |