The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
HI I want this table
Date | Account | Value | Type |
2023-10-05 | 2 | X | a |
2023-10-05 | 2 | Y | a |
2023-10-05 | 2 | Z | b |
2023-10-05 | 2 | F | b |
To result when inserted into a matrix
Date | Account | Value (Measure) |
2023-10-05 | 2 | X + Y |
2023-10-05 | 2 | Z |
2023-10-05 | 2 | F |
So a measure that aggregates Value if type is a but not if type is b, the ALL rows shapp be shown for a specific date and account.
Hi,
I am not sure if I understood your question correctly, but I tried to solve this by adding index column into the table like below.
Please check the below picture and the attached pbix file if it suits your requirement.
Expected result measure: =
VAR _minindex =
MINX (
FILTER ( ALL ( Data ), Data[Type] = MAX ( Data[Type] ) ),
CALCULATE ( SELECTEDVALUE ( Data[Index] ) )
)
RETURN
IF (
MAX ( Data[Type] ) = "a",
IF (
MAX ( Data[Index] ) = _minindex,
CALCULATE ( SUM ( Data[Value] ), FILTER ( ALL ( Data ), Data[Type] = "a" ) ),
BLANK ()
),
SUM ( Data[Value] )
)
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |