Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone,
I want to create a formula which count how many times a specific value shows up in the most recent date.
I made a sample set (see below). I want to know how many times '-1' shows up in the latest month (March). So the answer should be 3.
Date | Aspect | Value |
January 2020 | Responsibility | 1 |
January 2020 | Validity | 0 |
January 2020 | Satefy | -1 |
January 2020 | Guarantee | -1 |
February 2020 | Responsibility | 0 |
February 2020 | Validity | -1 |
February 2020 | Satefy | 0 |
February 2020 | Guarantee | 0 |
March 2020 | Responsibility | -1 |
March 2020 | Validity | -1 |
March 2020 | Satefy | -1 |
March 2020 | Guarantee | 1 |
Solved! Go to Solution.
Hi, @Anonymous
Try adding format function to “Table”[Date]:
measure =
var _sel = -1
return
CALCULATE(COUNT('Table'[Value]),FORMAT('Table'[Date],"MMMM YYYY")=FORMAT(TODAY(),"MMMM YYYY"),'Table'[Value]=_sel)
Best Regards,
Community Support Team _ Eason
measure =
var _sel = -1
return
calculate(count(table[Value]),Table[date] = format(today(),"MMMM YYYY"),table[value] =_sel)
Hi @amitchandak ,
Thanks for your reply.
I cannot see if the measure works, because Power BI shows me the following:
Cannot load the data for this visual
In DAX-comparisons it is not supported to make comparisons of values of type Date with values of type Text.
I don't understand this, since the column Value is the type 'Whole value' and not 'Text'.
Hi, @Anonymous
Try adding format function to “Table”[Date]:
measure =
var _sel = -1
return
CALCULATE(COUNT('Table'[Value]),FORMAT('Table'[Date],"MMMM YYYY")=FORMAT(TODAY(),"MMMM YYYY"),'Table'[Value]=_sel)
Best Regards,
Community Support Team _ Eason
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |