Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
My table looks like this and in my model has a 1:N relationship with my Calendar Table from Date to Date:
Category | Date | Dimension |
A | 1.1.2020 | Cat |
A | 3.2.2020 | Dog |
B | 1.1.2019 | Cat |
B | 1.3.2019 | Dog |
B | 1.6.2019 | Snake |
C | 1.1.2020 | Snake |
C | 2.1.2020 | Dog |
C | 3.1.2020 | Snake |
B | 1.1.2020 | Cat |
I want to count the number of Categories I have in each dimension for every date I choose to filter so that the output is like this for today's date
Dimension | Date | Count of Category |
Dog | 22.4.2020 | 1 |
Cat | 22.4.2020 | 1 |
Snake | 22.4.2020 | 1 |
But when I select another date, for example 1.st of February 2020 the result would be this
Dimension | Date | Count of Category |
Dog | 1.2.2020 | 0 |
Cat | 1.2.2020 | 2 |
Snake | 1.2.2020 | 1 |
So basically I need to count the rows in my Calendar table that fall between two dates corresponding to the Category and Dimension columns.
How can I achieve this usind DAX ?
What I've tried so far is using FILTER(), and ERLIER() but my DAX skills are not up to the task I'm afraid.
Solved! Go to Solution.
Hi, @Yggdrasill
You may modify 'var _date = SELECTEDVALUE('Calendar'[Date])' as 'var _date = MAX('Calendar'[Date])' and make other codes unchanged.
CountValue =
var _date = MAX('Calendar'[Date])
var _status = SELECTEDVALUE(Test[Status])
var tab =
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Case],
"MaxDate",
var c = [Case]
return
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALLSELECTED('Table'),
'Table'[Case] =c&&
'Table'[Date]<=_date
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"Status",
var c = [Case]
var md = [MaxDate]
return
MAXX(
FILTER(
ALLSELECTED('Table'),
'Table'[Case] = c&&
'Table'[Date] = md
),
[Status to]
)
)
var result =
COUNTROWS(
FILTER(
newtab,
[Status] = _status
)
)
return
IF(
ISBLANK(result),
0,
result
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Yggdrasill
I wonder if there is something wrong with the expected result on 2/1/2020. I assume that you want to calculated the number of distinct categories where the date is less than or equal to the selected date for each dimension.
Table:
Calendar:
Calendar = CALENDARAUTO()
There is no relationship between two tables.
You may create a measure as below.
Count =
var _date = SELECTEDVALUE('Calendar'[Date])
var _dimension = SELECTEDVALUE('Table'[Dimension])
return
CALCULATE(
DISTINCTCOUNT('Table'[Category]),
FILTER(
ALL('Table'),
'Table'[Dimension] = _dimension&&
'Table'[Date]<=_date
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for taking the time Allan but this doesn't seem to return the desired output.
For 1.st of February the active cases should be 2 Cats and 1 snake. (please note I'm using dd.mm.yyyy format)
Furthermore the measure doesn't seem to work.
My table is actually like this:
Case | Date | Status from | Status to |
A | 1.1.2020 | New | Open |
A | 20.1.2020 | Open | Closed |
B | 1.1.2020 | New | Open |
B | 13.1.2020 | Open | Pending |
B | 1.2.2020 | Pending | Closed |
I need to count the dates between each status changes for each and every Case and be able to visualize it like so for all cases
Hi, @Yggdrasill
Based on your data, I created data to reproduce your scenario.
Table:
Test:
Calendar:
Calendar = CALENDARAUTO()
You may create a measure as below.
CountValue =
var _date = SELECTEDVALUE('Calendar'[Date])
var _status = SELECTEDVALUE(Test[Status])
var tab =
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Case],
"MaxDate",
var c = [Case]
return
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALLSELECTED('Table'),
'Table'[Case] =c&&
'Table'[Date]<=_date
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"Status",
var c = [Case]
var md = [MaxDate]
return
MAXX(
FILTER(
ALLSELECTED('Table'),
'Table'[Case] = c&&
'Table'[Date] = md
),
[Status to]
)
)
var result =
COUNTROWS(
FILTER(
newtab,
[Status] = _status
)
)
return
IF(
ISBLANK(result),
0,
result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Allan ! This is something I can use ! Brilliant !
I want to note though that the calculations are slow because I have more than 2000 Cases and a span of more than 5 years.
Also, the count breaks when I use date hierachy in the visuals.
Is it possible to use the end of each date as a breaking point when I want to up the date granularity ?
For example if I use a barchart with Years, I would see the final count for each year ?
Hi, @Yggdrasill
You may modify 'var _date = SELECTEDVALUE('Calendar'[Date])' as 'var _date = MAX('Calendar'[Date])' and make other codes unchanged.
CountValue =
var _date = MAX('Calendar'[Date])
var _status = SELECTEDVALUE(Test[Status])
var tab =
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Case],
"MaxDate",
var c = [Case]
return
CALCULATE(
MAX('Table'[Date]),
FILTER(
ALLSELECTED('Table'),
'Table'[Case] =c&&
'Table'[Date]<=_date
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"Status",
var c = [Case]
var md = [MaxDate]
return
MAXX(
FILTER(
ALLSELECTED('Table'),
'Table'[Case] = c&&
'Table'[Date] = md
),
[Status to]
)
)
var result =
COUNTROWS(
FILTER(
newtab,
[Status] = _status
)
)
return
IF(
ISBLANK(result),
0,
result
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi You could just use the category as value on your visual and select its aggregation as count . Alternatively create a new measure as
That won't work as I need to count all the dates between the two dates. Your suggestion will only count for the dates registered in the transaction table - Hence, I need to count the dates in the Calendar table in order to have the value 1 for each Category that falls between the two dates.
You can look at this model as a demo for a Ticket system where the status of the ticket for company A, B, C... can have the value Open, Pending, In Order and Closed where each status has it's own date.
I need to be able to count all Pending orders from any date I choose to view at any given time and even filter it by company A, B, C etc
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |