Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to get the number of occurrences of each DQV Category. The QDV Category is a result of a measure based on the Overall Average score. For instance, "Meeting Standard" appears only once based on the selected Filter but I am getting 0.
Any help would be highly appreciated.
Solved! Go to Solution.
Hi @tbhadmus
Following is the general idea. In order to include the category slicer with the formula I need more information about this slicer and the measure itself.
Count =
VAR T1 =
SUMMARIZE ( TableName, TableName[Country], TableName[IP] )
VAR T2 =
ADDCOLUMNS ( T1, "@QDV_Category", [QDV Category] )
VAR T3 =
FILTER ( T2, [@QDV_Category] = "Meeting Standard" )
RETURN
COUNTROWS ( T3 )
@tamerj1 thanks for your response. The problem was with my filters and I already figured it out. Thanks a lot for the help!
I have another challenge which I need help with.
In the table above, I want to calculate the NextDVDate based on the values in the Average M&E column. The Average M&E column is a measure and the condition for the calculation is as below:
But I am getting an error in my calculation. Any help will be higlhy appreciated.
Thanks in advance.
Hi @tbhadmus
please try
NextDVDate =
VAR avgScore = [Average M&E]
VAR date_ =
SELECTEDVALUE ( 'Data Element Values'[PeriodDate] )
RETURN
SWITCH (
TRUE (),
avgScore >= 95
&& avgScore <= 100, ( date_ + 365 ),
avgScore >= 80
&& avgScore <= 94, ( date_ + 365 ),
avgScore < 80, ( date_ + 90 ),
BLANK ()
)
The problem here is that the "date_" variable is returning Blank().
Hi @tamerj1, thanks for your swift response.
I tried the above formula but I got Blank as my result. Below is the table using the formula above:
Looks like PeriodDate is a measur? In this case no need for SELECTEDVALUE just refer to the measure directly
Column PeriodDate is a Calculated Column. Find below:
Please try by wrapping SELECTEDVALUE with CALCULATE
I did that as well but I still got Blank()
Are you creating a calculated column or a measure?
I'm creating a measure. The "PeriodDate" is a calculated column.
What are slicing by in your matrix?
I'm slicing by the countries
Can you please change SELECTEDVALUE to MAX or MIN. See first which date yiu get. RETURN date_
Great! MIN seems to work, it returns the same date as the PeriodDate which is what I am trying to do.
Hi, @tbhadmus
Can you provide some sample data? And what you expect the output to be, which can be shown in pictures.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I tried the formula but it's the same as what I initially had:
Hi @tbhadmus
Following is the general idea. In order to include the category slicer with the formula I need more information about this slicer and the measure itself.
Count =
VAR T1 =
SUMMARIZE ( TableName, TableName[Country], TableName[IP] )
VAR T2 =
ADDCOLUMNS ( T1, "@QDV_Category", [QDV Category] )
VAR T3 =
FILTER ( T2, [@QDV_Category] = "Meeting Standard" )
RETURN
COUNTROWS ( T3 )
Hi tamerj1,
thanks for your response.
The slicer represents the Fiscal Year. If I select a fiscal year, the total occurrence of the DQV category should be displayed.
@tbhadmus
Ok then the same code should work. Please try and let me know. Thank you
The code works if no filter is selected, but when I select a filter, it gives me a value of zero instead of 1 from the picture I posted.
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |