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.
Let's assume I have the below dataset.
What I need to create the below matrix where if it is the beginning or month end, I aggregate A or B in Category 1 but if it is any other day in a month but 1st or last, I am tagging A or B in Category 2 and calculate COUNT. I guess I need to use SWITCH, is not it?
Solved! Go to Solution.
@ekaponkratova -
Maybe this is what you were looking for?
I created a Calculated Column, to get labels for Columns as:
Column = VAR startMonth = STARTOFMONTH(TableName[date]) VAR endMonth = ENDOFMONTH(TableName[date].[Date]) RETURN IF( TableName[date]=startMonth || TableName[date]=endMonth, "Category 1", "Category 2" )
Then I created a Measure as:
Measure = SWITCH ( TRUE (), STARTOFMONTH ( TableName[date] ) = MAX ( TableName[date] ) || ENDOFMONTH ( TableName[date].[Date] ) = MAX ( TableName[date] ), SUM ( TableName[quantity] ), STARTOFMONTH ( TableName[date] ) <> MAX ( TableName[date] ) || ENDOFMONTH ( TableName[date].[Date] ) <> MAX ( TableName[date] ), COUNTROWS ( TableName ) )
Results in the left matrix:
Proud to be a Super User!
@ekaponkratova -
Maybe this is what you were looking for?
I created a Calculated Column, to get labels for Columns as:
Column = VAR startMonth = STARTOFMONTH(TableName[date]) VAR endMonth = ENDOFMONTH(TableName[date].[Date]) RETURN IF( TableName[date]=startMonth || TableName[date]=endMonth, "Category 1", "Category 2" )
Then I created a Measure as:
Measure = SWITCH ( TRUE (), STARTOFMONTH ( TableName[date] ) = MAX ( TableName[date] ) || ENDOFMONTH ( TableName[date].[Date] ) = MAX ( TableName[date] ), SUM ( TableName[quantity] ), STARTOFMONTH ( TableName[date] ) <> MAX ( TableName[date] ) || ENDOFMONTH ( TableName[date].[Date] ) <> MAX ( TableName[date] ), COUNTROWS ( TableName ) )
Results in the left matrix:
Proud to be a Super User!
And you are officially genius. Just question. Why do we need the 2nd measure? Cannot I just use quantity?
I thought you needed a SUM([quantity]) when Category 1 othewise COUNTROWS. The Measure is trying to do that.
Proud to be a Super User!
Hm, I still need to test, but I what I did, I re-used your category and then, just dropped quantity to values and used sum as aggregation.
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 |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |