Forum Discussion
Summarize a measure
Hey Folks,
I have a problem with a data table and cant figure out the problem. It would help me a lot if you have an idea on this issue.
There is a data table like this
| Name | date | value |
| a | 2023-10-20 | 1 |
| b | 2023-10-20 | 1 |
| a | 2023-10-21 | 1 |
| a | 2023-10-22 | 1 |
| a | 2023-10-22 | 1 |
he value in the value Column is always 1, the values in the other rows can be different.
In this example the result for "a" = 4, "b" = 1
For my Visualition I need a table which looks like this:
| Category | Sum |
| 1-3 days | 1 |
| 4-10 day | 1 |
I ve managed to create a measure which shows me the category text ("1-3 days", "4-10 days") if I combine it with the "Name" column. But I cant summarize just the Category column.
Is there any way to solve this?
Greetings
Rico
4 Replies
- Greg_Deckler
Community Champion
LBarto90 If I am understanding correctly, perhaps something like this:
Measure = VAR __Category = MAX('CategoriesTable'[Category]) VAR __Table = SUMMARIZE('Table', [Name], "__Count", COUNTROWS('Table')) VAR __One2Three = COUNTROWS(FILTER(__Table, [__Count]<4)) VAR __MoreThan4 = COUNTROWS(FILTER(__Table, [__Count]>3)) VAR __Result = IF(__Category = "1-3", __One2Three, __MoreThan4) RETURN __Result "Category" is not a colomn in the data table. "Category" is the value which is Calculated from the measure. So I guess this wont work.
This is the DAX of the measure:
Anzahl krank_Kategorie =SWITCH(TRUE(),[Anzahl krank] <= 3, "bis 3 Tage",AND([Anzahl krank] > 3, [Anzahl krank] <= 14), "4 bis 14 Tage","0")I need this for an absence overview. I want to know how many absence days have been totally spent in the 1-3 days range, 4-10 days range and so on.
- Greg_Deckler
LBarto90 OK, try this then:
Measure = VAR __Category = [Anzahl krank_Kategorie] VAR __Table = SUMMARIZE('Table', [Name], "__Count", COUNTROWS('Table')) VAR __One2Three = COUNTROWS(FILTER(__Table, [__Count]<4)) VAR __MoreThan4 = COUNTROWS(FILTER(__Table, [__Count]>3)) VAR __Result = IF(__Category = "1-3", __One2Three, __MoreThan4) RETURN __Result
- SpoilerGreg_Deckler
If you need further information pls let me know.
